Entry

What is the query one would perform to find out the text of a stored procedure in Oracle?

Feb 29th, 2000 19:33
Richard Close, Aaron Young,

Here's a SQL*Plus script I use for PROCEDURES (edit to suit your needs):

----- start here -----
rem ******************************************
rem *
rem * Dump procedure definition.
rem * Called from parent script with arguments
rem * <SCHEMA> <PROCEDURENAME> <ROOT DIR> <TIMESTAMP>
rem *
rem ******************************************

set termout on
PROMPT Dumping procedure definition for &1..&2 ...
set termout off

set linesize 300
set pagesize 0
set feedback off
set trimspool on
set verify off
set serveroutput on
col COLDEF format A60

HOST if not exist &3.&1 md &3.&1

SPOOL &3.&1\&2..SQL
PROMPT rem ****** PROCEDURE &1..&2 *****************
PROMPT CREATE OR REPLACE
select text
from all_source
where name = upper('&2') and owner = upper('&1')
and type = 'PROCEDURE'
order by line;
SPOOL OFF
----- cut here ------

And here's one for PACKAGES ...

----- start here -----
rem ******************************************
rem *
rem * Dump package definition.
rem * Called from parent script with arguments
rem * <SCHEMA> <PACKAGENAME> <ROOT DIR> <TIMESTAMP>
rem *
rem ******************************************

set termout on
PROMPT Dumping package definition for &1..&2 ...
set termout off

set linesize 300
set pagesize 0
set feedback off
set trimspool on
set verify off
set serveroutput on
col COLDEF format A60

HOST if not exist &3.&1 md &3.&1

SPOOL &3.&1\&2..SQL
PROMPT rem ****** PACKAGE &1..&2 *****************
PROMPT
PROMPT CREATE OR REPLACE
select text
from all_source
where name = upper('&2') and owner = upper('&1')
and type = 'PACKAGE'
order by line;
PROMPT
PROMPT rem ****** PACKAGE BODY &1..&2 *****************
PROMPT CREATE OR REPLACE
select text
from all_source
where name = upper('&2') and owner = upper('&1')
and type = 'PACKAGE BODY'
order by line;
SPOOL OFF
----- cut here ------