Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Extacting stored packages from database
--
select 'set echo on ' from dual; select 'spool bld_packages.lst' from dual; select 'rem Building packages definition' from dual;
SELECT NAME SORT1, LINE SORT2,
'Create or Replace ' || TEXT
FROM USER_SOURCE
WHERE TYPE = 'PACKAGE'
AND LINE = 1
UNION
SELECT NAME SORT1, LINE SORT2,
TEXT
FROM USER_SOURCE
WHERE TYPE = 'PACKAGE'
AND LINE > 1
UNION
SELECT NAME SORT1, 999999 SORT2,
'/'
FROM USER_SOURCE
WHERE TYPE = 'PACKAGE'
AND LINE = 1
ORDER BY 1,2;
spool off
SPOOL BLD_PACK_BODY.sql
select 'set echo on ' from dual; select 'spool bld_pack_body.lst' from dual; select 'rem Building packages body definition' from dual;
SELECT NAME SORT1, LINE SORT2,
'Create or Replace ' || TEXT
FROM USER_SOURCE
WHERE TYPE = 'PACKAGE BODY'
AND LINE = 1
UNION
SELECT NAME SORT1, LINE SORT2,
TEXT
FROM USER_SOURCE
WHERE TYPE = 'PACKAGE BODY'
AND LINE > 1
UNION
SELECT NAME SORT1, 999999 SORT2,
'/'
FROM USER_SOURCE
WHERE TYPE = 'PACKAGE BODY'
AND LINE = 1
ORDER BY 1,2;
spool off
SPOOL BLD_PROC.sql
select 'set echo on ' from dual; select 'spool bld_proc.lst' from dual; select 'rem Building procedures definition' from dual;
SELECT NAME SORT1, LINE SORT2,
'Create or Replace ' || TEXT
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE = 1
UNION
SELECT NAME SORT1, LINE SORT2,
TEXT
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE > 1
UNION
SELECT NAME SORT1, 999999 SORT2,
'/'
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE = 1
ORDER BY 1,2;
spool off
SPOOL BLD_FUNC.sql
select 'set echo on ' from dual; select 'spool bld_func.lst' from dual; select 'rem Building functions definition' from dual;
SELECT NAME SORT1, LINE SORT2,
'Create or Replace ' || TEXT
FROM USER_SOURCE
WHERE TYPE = 'FUNCTION'
AND LINE = 1
UNION
SELECT NAME SORT1, LINE SORT2,
TEXT
FROM USER_SOURCE
WHERE TYPE = 'FUNCTION'
AND LINE > 1
UNION
SELECT NAME SORT1, 999999 SORT2,
'/'
FROM USER_SOURCE
WHERE TYPE = 'FUNCTION'
AND LINE = 1
ORDER BY 1,2;
spool off
"The Views expressed here are my own and not necessarily those of Oracle Corporation"
<akkha_at_my-deja.com> wrote in message news:81449h$1fg$1_at_nnrp1.deja.com...
> Hi, > > I want to extract the code on our stored packages, procedures > and functions from Oracle Database in a nice format such > that it is ready for recompiling without any further editing. > Is there any utlity on UNIX environment which could do this? > > My purpose is for production control. Whenever > a new version of the stored package is going to be implemented, > I would like to extract the current version in > case a rollback is needed. I could come up my own script > but need some editing. The script from the book > 'Oracle Script' by Lomasky won't work either. The one from > TOAD is good but it is not available in command mode and > not available on UNIX environment anyway. Also won't work > if sql*net is not opened up for the host. > Moreover, we distribute 'wrapped' code to our client. > So I could not even edit the codes . > > Any help or suggestion is welcome. > > Anthony > > > Sent via Deja.com http://www.deja.com/ > Before you buy.Received on Sat Nov 20 1999 - 14:28:41 CST