Re: Extract Stored Procs & Function Code Regularly
Date: Wed, 23 Dec 1998 21:26:36 GMT
Message-ID: <75rn69$63d$1_at_nnrp1.dejanews.com>
In article <367EDD7A.A83DB7FA_at_wesleyan.edu>,
Tom D <tdimauro_at_wesleyan.edu> wrote:
> I would greatly appreciate if anyone knows of a way either through
> PL/SQL Code or utility that I could extract the PL/SQL of an existing
> database. For instance on a nightly basis I would like to extract the
> DDL's of all the stored procs and functions on a given Oracle Instance.
>
Here is a sql*plus script I use to extract a particular procedure. It could be easily adapted to extract them all.
------%<--------------%<------------------------%<----------------------------SET PAUSE OFF
SET TERMOUT OFF
SET VERIFY OFF
COLUMN today NEW_VALUE _date
COLUMN rightnow NEW_VALUE _time
COLUMN program NEW_VALUE _prog
SELECT TO_CHAR(sysdate,'fmMonth DD, YYYY') today,
TO_CHAR(sysdate,'HH:MIam') rightnow,
UPPER('&1') program
FROM dual
/
COLUMN database_name NEW_VALUE _name
SELECT name database_name
FROM v$database
/
COLUMN timestamp NEW_VALUE _stamp
SELECT TO_CHAR(TO_DATE(timestamp,'YYYY-MM-DD:HH24:MI:SS'),'DD-MON-YY HH24:MI:SS') timestamp
FROM all_objects
WHERE UPPER(object_name) = UPPER('&1') /
SET LINESIZE 156
SET WRAP OFF
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET LONG 8000
COLUMN SOURCE_LINE FORMAT A255
COLUMN prog FORMAT A40
COLUMN stamp FORMAT A19
COLUMN name FORMAT A8
SPOOL LIS:&1..&_name._LIS
SELECT '&_prog' prog, '&_stamp' stamp, '&_name' name FROM DUAL
/
SELECT RTRIM(TEXT) SOURCE_LINE FROM ALL_SOURCE WHERE UPPER(NAME)=UPPER('&1') /
SPOOL OFF
EXIT
---------%<------------------------%<--------------------------%<--------------
The SELECT RTRIM(TEXT) is the line that actually retrieves the sources from the database. The rest of the script is bells and whistles that date-time stamp the printout, put the database name in the extension of the spool file so I know if I am looking at development or production code, and include the date-time stamp from the database so I know when the procedure/function in question was last modified. I have a modification of this script which produces a "prettier" output with titles and page breaks. The output of this script can be used, with minor modification (adding the words "create or replace " for instance), to recreate the source code file if it was ever lost.
Howard Parks
1 Peter 4:10
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Dec 23 1998 - 22:26:36 CET