Search code examples
sqloracle-databasefunctionoracle12cfunction-definition

How to select function definitions as a query?

I would like to select the PL/SQL definition of all functions in a schema as a query. The reason being I want to query all my functions for specific text.

I can do this for views like so:

select * from all_views

However, I'm not sure how to do this with functions.

Can it be done?

Solution

  • To list all functions, you can query the ALL_OBJECTS dictionary view :

    SELECT * FROM all_objects WHERE object_type = 'FUNCTION';

    This will give you all the functions available to the current user (DBA_OBJECTS lists all objects in the database).

    Then, you can retrieve the text source of the function from view ALL_SOURCE :

    SELECT o.object_name, s.line, s.text 
FROM all_objects o
INNER JOIN all_source s 
    ON s.name = o.object_name AND s.type = o.object_type AND s.owner = o.owner
WHERE o.object_type = 'FUNCTION';