Getting the list of Stored Procedures in Database

[Edimator]

Hi, i need a list of the all Stored Procedures (and parameters) in the database. The database is in 8.0.5 and access only through SQL Plus.

 

[sem]

select object_type,object_name from user_objects where object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION')

As for parameters, try to utilize DBMS_DESCRIBE package Regards, Dima

 

[BJCooperIT]

Below is a quick and dirty script that will give you a list:

select all_objects.owner,
all_objects.object_name,
substr(dba_source.text,1,100) text
from sys.dba_source,
sys.all_objects
where all_objects.owner = dba_source.owner
and all_objects.object_name = dba_source.name
and all_objects.object_type
in ('FUNCTION','PROCEDURE','PACKAGE')
and all_objects.owner='SCHEMA_NAME'
and dba_source.line <=
(select min (ds.line)
from sys.dba_source ds
where ds.owner = all_objects.owner
and ds.name = all_objects.object_name
and (UPPER(ds.text) like '%BEGIN%' or ds.text like '%)%'))
order by dba_source.owner,
dba_source.name,
dba_source.line;

Hope it helps,
Barbara

 

[Edimator]

Thanks a lot, very usefull. Is possible get the code of one stored procedure from sql*plus.

 

[BJCooperIT]

set long 5000
set linesize 130
set verify on
column text format a130 heading &quot;Procedure Text&quot;

select dba_source.text
from sys.dba_source,
sys.all_objects
where all_objects.owner = dba_source.owner
and all_objects.object_name = dba_source.name
and all_objects.object_type = 'PROCEDURE'
and all_objects.owner='&SCHEMA_NAME'
and all_objects.object_name = '&PROCEDURE_NAME'
order by dba_source.owner,
dba_source.name,
dba_source.line;

I haven't had the opportunity to test this script but it should get you what you are looking for. Developer tools such as Toad, SQL Navigator, and PL/SQL Developer can do this with a click of the mouse...gosh I love the information age!

Consultant/Custom Forms & PL/SQL
Oracle 8.1.7 - Windows 2000