Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Extacting stored packages from database

Re: Extacting stored packages from database

From: Yass Khogaly <ykhogaly_at_us.oracle.com>
Date: Sat, 20 Nov 1999 12:28:41 -0800
Message-ID: <816smb$gae$1@inet16.us.oracle.com>


--



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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US