Re: Creating text files from pl/sql packages and procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 08 Nov 1999 08:25:04 -0500
Message-ID: <484mOHlIaOh0CBoBkhCeMNeIj9Bq_at_4ax.com>

A copy of this was sent to Peter Kellner <peter_at_kellner.com> (if that email address didn't require changing) On Sun, 07 Nov 1999 18:15:56 -0800, you wrote:

>Is there any oracle utility that would help in taking an PL/SQL stored
>procedure and dumping it to a text file? Or even a pointer to some
>sql would be helpful that does this.
>
>thanks

This is a sqlplus script I call "getcode" that does that:

---

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'), 'PACKAGE BODY-1', '/'||chr(10 ), null) ||

       decode(line,1,'create or replace ', '' ) || text text   from user_source
 where name = upper('&&1')
 order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100

----------------------- EOF -------------------------------------------

You would run it then as:

SQL> _at_getcode Your_procedure_package_or_function_name

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
[Quoted] Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
[Quoted] Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Mon Nov 08 1999 - 14:25:04 CET