Home » RDBMS Server » Server Administration » How do I generate a SQL script file for creation of existing DB Objects (merged 4)
How do I generate a SQL script file for creation of existing DB Objects (merged 4) [message #397351] Fri, 10 April 2009 04:33 Go to next message
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

Re: How do I generate a SQL script file for creation of existing DB Objects (merged 3) [message #397356 is a reply to message #397351] Fri, 10 April 2009 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbms_metadata.get_ddl procedure.

Regards
Michel
Re: How do I generate a SQL script file for creation of existing DB Objects (merged 4) [message #397385 is a reply to message #397351] Fri, 10 April 2009 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to post the same question 3 hours later in another forum, the topics will be merged.

Please read OraFAQ Forum Guide.

Regards
Michel
Re: How do I generate a SQL script file for creation of existing DB Objects (merged 4) [message #397472 is a reply to message #397385] Fri, 10 April 2009 23:59 Go to previous messageGo to next message
aditya.garg@accenture.com
Messages: 8
Registered: March 2009
Junior Member
Hi Michel,

I also tried the get_ddl command the problem i am having is either i can extract DB objects 1 by 1 or all the objects from schema.

I am trying is to extract 5000 DB objects without touching any of the other unwanted DB objects.

Hope you can help.
Re: How do I generate a SQL script file for creation of existing DB Objects (merged 4) [message #397473 is a reply to message #397351] Sat, 11 April 2009 00:02 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I am trying is to extract 5000 DB objects without touching any of the other unwanted DB objects.
Write single SQL to write extraction SQL.
Re: How do I generate a SQL script file for creation of existing DB Objects (merged 4) [message #397474 is a reply to message #397473] Sat, 11 April 2009 00:12 Go to previous messageGo to next message
aditya.garg@accenture.com
Messages: 8
Registered: March 2009
Junior Member
Hi,

Can you be little precise about your suggestion.
I have even tried to create a table for testing purpose and use that table in place of user_objects... but it is not working at all..
Re: How do I generate a SQL script file for creation of existing DB Objects (merged 4) [message #397480 is a reply to message #397472] Sat, 11 April 2009 01:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
I also tried the get_ddl command the problem i am having is either i can extract DB objects 1 by 1 or all the objects from schema.

I am trying is to extract 5000 DB objects without touching any of the other unwanted DB objects.

Not sure what the problem is.
You can "exclude" the unwanted the objects or
"include" the wanted objects (which ever is smaller in count) in the where clause.
It can all be scripted.
There is no point in building a separate table for this.
Find a pattern and use like or not like or even regular expressions to filter the data you want.

Re: How do I generate a SQL script file for creation of existing DB Objects (merged 4) [message #397492 is a reply to message #397480] Sat, 11 April 2009 07:52 Go to previous message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


>>I have even tried to create a table for testing purpose and use that table in place of user_objects... but it is not working at all..

What you tried?? Post your SQL Script.

Mr Mic already given solution for you.

Also If you want generate script from TOAD and SQL Developer.

Babu
Previous Topic: Oracle 10.2.0.4 on SAN Storage.
Next Topic: error in identifying file 'C:\oracle\product\10.2.0\db_1/dbs/spfilegrasim.ora'
Goto Forum:
  


Current Time: Fri Dec 02 14:33:50 CST 2016

Total time taken to generate the page: 0.33580 seconds