What SQL would I need to use to list all the stored procedures on an Oracle database?
If possible I'd like two queries:
- list all stored procedures by name
- list the code of a stored procedure, given a name
views:
296answers:
2
+4
A:
The DBA_OBJECTS view will list the procedures (as well as almost any other object):
SELECT owner, object_name
FROM dba_objects
WHERE object_type = 'PROCEDURE'
The DBA_SOURCE view will list the lines of source code for a procedure in question:
SELECT line, text
FROM dba_source
WHERE owner = ?
AND name = ?
AND type = 'PROCEDURE'
ORDER BY line
Note: Depending on your privileges, you may not be able to query the DBA_OBJECTS and DBA_SOURCE views. In this case, you can use ALL_OBJECTS and ALL_SOURCE instead. The DBA_ views contain all objects in the database, whereas the ALL_ views contain only those objects that you may access.
Adam Paynter
2009-06-05 13:53:40
You want to do object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE') to get all the possible source code.
Thomas Jones-Low
2009-06-05 14:04:57
Sorry, total Oracle noob here. I can't seem to see that view/table. Does that mean I'm logged in as the wrong user?
Mike McQuaid
2009-06-05 14:15:11
You may not have the privileges necessary to query those views. You can try ALL_OBJECTS and ALL_SOURCE instead.
Adam Paynter
2009-06-05 14:16:18
Worked with ALL_*, want to update the answer to have that and I'll accept it?
Mike McQuaid
2009-06-05 14:25:25
I kept the DBA_ views in the example, but I added a note explaining the ALL_ views. The DBA_ views are still preferable if you have access to them. :)
Adam Paynter
2009-06-05 14:31:12
A:
If you want to get all the calls to introspect stored procedures (parameters, etc), you can pull it out of this open source package:
http://code.google.com/p/orapig
OraPIG is the Oracle Python Interface Generator. It introspects oracle packages and generates python wrappers for them.
Mark Harrison
2009-06-06 22:39:32