list procedures

[fordikon]

how to list procedures for a schema in database through SQL

[mbartov]

Try this:

SELECT object_type, owner, object_name, status, created
from dba_objects
Where owner = 'SYSTEM'
AND object_type = 'PROCEDURE'
order BY object_name;

If you want to list the contents of a procedure:
SELECT text
from DBA_SOURCE
where type='PROCEDURE'
and owner='SYSTEM'
and name='ORA$_SYS_REP_AUTH'
order by line;

I posted an integrated set of GUI tools to manage, monitor and tune the Oracle database. One of the many functions is to display object information including procedures for a selected schema. The link is http://www.barsoft.net

[gbrabham]

Here's a script I use. It will produce the SQL necessary
to create the Procedures ... Save the script to a file
somewhere and write a SQL statement to build the
procedures (change the spool to write to your directory)

Get a list of procedures by:
select object_name from all_objects
where object_type = 'PROCEDURE'
and owner = 'SCHEMANAME';

Use the script below as a file called from SQLPlus as:

sql> select '@c:\...\new_file_name '||object_name||' OWNER'
from all_objects
where object_type = 'PROCEDURE'
and owner = 'OWNERNAME';

This will produce 1 line for each procedure for
the schema. You may need to spool the output to a file
and execute that file... and then run the above: ie,

sql> spool c:\...\build_procedures.sql
sql> /

sql> @c:\...\build_procedures.sql

--------------------- script ----------------------------

set echo off verify off term on feedback off pagesize 0
select 'Creating procedure build script...' from DUAL;

--accept procedure_name prompt "Enter the name of the procedure: "
--accept procedure_owner prompt "Enter procedure owner: "
DEFINE PROCEDURE_NAME = &1;
DEFINE PROCEDURE_OWNER = &2;
set term off

drop table PROC_TEMP
/

create table PROC_TEMP (
Lineno NUMBER,
Id_Owner VARCHAR2(30),
Id_Name VARCHAR2(30),
Text VARCHAR2(2000))
/

declare
cursor PROC_CURSOR is
select Owner,
Name,
Type,
Line,
Text
from DBA_SOURCE
where Type = 'PROCEDURE'
and Owner = UPPER('&&procedure_owner')
and Name like UPPER('&&procedure_name')
order by Owner, Name, Type, Line;

Lv_Owner DBA_SOURCE.Owner%TYPE;
Lv_Name DBA_SOURCE.Name%TYPE;
Lv_Type DBA_SOURCE.Type%TYPE;
Lv_Text DBA_SOURCE.Text%TYPE;
Lv_Line DBA_SOURCE.Line%TYPE;

Lv_String VARCHAR2(2000);
Lv_string2 VARCHAR2(2000);
Lv_Lineno NUMBER := 0;

procedure WRITE_OUT(P_Line INTEGER, P_Owner VARCHAR2, P_Name VARCHAR2,
P_String VARCHAR2) is
begin
insert into PROC_TEMP (Lineno, Id_Owner, Id_Name, Text)
values (P_Line,P_Owner,P_Name,P_String);
end;

begin
open PROC_CURSOR;
Lv_Lineno := 1;
loop
fetch PROC_CURSOR into Lv_Owner,
Lv_Name,
Lv_Type,
Lv_Line,
Lv_Text;
exit when PROC_CURSOR%NOTFOUND;

if (Lv_Line = 1)
then
Lv_String := 'CREATE OR REPLACE PROCEDURE ';
WRITE_OUT(Lv_Lineno, Lv_Owner, Lv_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;

Lv_String := SUBSTR(Lv_Text,LENGTH(Lv_Type)+1,
(LENGTH(Lv_Text) - LENGTH(Lv_Type)));
Lv_String := Lv_Owner || '.' || LTRIM(Lv_String);
WRITE_OUT(Lv_Lineno, Lv_Owner, Lv_Name, Lv_String);
Lv_Lineno := Lv_Lineno + 1;
else
WRITE_OUT(Lv_Lineno, Lv_Owner, Lv_Name, Lv_Text);
Lv_Lineno := Lv_Lineno + 1;
end if;
end loop;
WRITE_OUT(Lv_Lineno, Lv_Owner, Lv_Name, '/');
close PROC_CURSOR;
end;
/
spool C:\directorypath\&&procedure_name..sql
select RTRIM(Text )
from PROC_TEMP
order by Id_Owner, Id_Name, Lineno
/
spool off