Get a list of all functions and procedures in an Oracle database

oracle plsql oracle9i
AJM picture AJM · Nov 30, 2009 · Viewed 283.5k times · Source

I'm comparing three Oracle schemas.

I want to get a list of all the functions and procedures used in each database. Is this possible via a query? (preferably including a flag as to whether they compile or not)

Ideally it would be great to have a single query with a flag that states whether the function/procedure is in each schema. But even just the first bit would be better than manually inspecting each schema.

Answer

Erich Kitzmueller picture Erich Kitzmueller · Nov 30, 2009 
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE')

The column STATUS tells you whether the object is VALID or INVALID. If it is invalid, you have to try a recompile, ORACLE can't tell you if it will work before.

Related questions

Oracle PL/SQL - How to create a simple array variable?

I'd like to create an in-memory array variable that can be used in my PL/SQL code. I can't find any collections in Oracle PL/SQL that uses pure memory, they all seem to be associated with tables. I'm looking …

Read More
UTL_FILE.FOPEN() procedure not accepting path for directory?

I am trying to write in a file stored in c:\ drive named vin1.txt and getting this error .Please suggest! > ERROR at line 1: ORA-29280: invalid > directory path ORA-06512: at > "SYS.UTL_FILE", line 18 ORA-06512: at > "…

Read More
Remove all characters after a specific character in PL/SQL

How do I get a substring from this example value: john.abc_1234 I want it to return john.abc.So basically we need to remove all the information after _. More examples: 1234_abc

Read More