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.

Solution 1

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.

Solution 2

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

Solution 3

 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

Comments

[Bitterblue]

'TABLE' Can someone give a hint how to delete functions/procedures/packages ?

[Erich Kitzmueller]

drop function xxx, drop procedure xyz, drop package foobar

[Erich Kitzmueller]

Someone suggested an edit to my answer, so that the SELECT statement would only select OBJECT_NAME instead of *. Anyway, this edit has been rejected (by reviewers other than me); IMO, at least, OBJECT_NAME, OBJECT_TYPE, STATUS should be selected to get useful output. That said, SELECT * doesn't hurt in this context.

[LifeiSHot]

If you have multiple users, and only like to list out objects on that user, just add in another clause "AND OWNER =[THAT USER NAME] ". Example for "user1": SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE') AND OWNER = 'user1'

[cartbeforehorse]

This answer is insufficient if you want to compare functions and procedures within a package.