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

Question

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

User · Accepted Answer

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

User · Answer

SELECT   FROM all procedures WHERE OBJECT TYPE IN   FUNCTION   PROCEDURE   PACKAGE     and owner    Schema name  order by object name  here  Schema name  is a name of schema  example i have a schema named PMIS  so the example will be SELECT   FROM all procedures WHERE OBJECT TYPE IN   FUNCTION   PROCEDURE   PACKAGE    and owner    PMIS  order by object name   Ref  https   www plsql co list-all-procedures-from-a-schema-of-oracle-database html

User · Answer

Do a describe on dba arguments  dba errors  dba procedures  dba objects  dba source  dba object size  Each of these has part of the pictures for looking at the procedures and functions   Also the object type in dba objects for packages is  PACKAGE  for the definition and  PACKAGE BODY  for the body   If you are comparing schemas on the same database then try   select   from dba objects     where schema name    ASCHEMA        and object type in    PROCEDURE    PACKAGE    FUNCTION    PACKAGE BODY    minus select   from dba objects  where schema name    BSCHEMA     and object type in    PROCEDURE    PACKAGE    FUNCTION    PACKAGE BODY      and switch around the orders of ASCHEMA and BSCHEMA   If you also need to look at triggers and comparing other stuff between the schemas you should take a look at the Article on Ask Tom about comparing schemas

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

Examples related to oracle

Examples related to plsql

Examples related to oracle9i