| How do I generate a SQL script file for creation of existing DB Objects (merged 4) [message #397351] |
Fri, 10 April 2009 04:33  |
aditya.garg@accenture.com
Messages: 8
Registered: March 2009
|
Junior Member |
|
|
Hi All,
I have searched a lot for the help in all the related Oracle forums. The below script i got from ASKTOM forums...
--------------------------------------------------------
---------------------------------------------------------
set termout off
set heading off
set feedback off
set linesize 50
spool xtmpx.sql
select '@getcode ' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
spool getallcode_INSTALL
select '@' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
@xtmpx.sql
is what I use. creates a script that calls the getcode script one by one to get the code out where
getcode is:
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
-------------------------------------------------------
-------------------------------------------------------
In my case i have only few (around 5000 packages/ Procedures) to be extracted. If i am using the above query it will obviously generate much more packages.
I would like any suggestions whether i can either create a custom table and put all the required DB objects to be extracted inthat table and use that table in the above query rather than user_objects.
Please help me in this
Aditya Garg
[Updated on: Fri, 10 April 2009 07:44] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
