Home » RDBMS Server » Backup & Recovery » extract of Procedures (oracle 10g)
extract of Procedures [message #456733] Wed, 19 May 2010 06:47 Go to next message
garimella_aditya
Messages: 8
Registered: May 2007
Location: India
Junior Member
Hi,
I would like to extract the stored procedures code to respective file names.
For Example if I have 100 procedures then want to extract them into 100 .sql files.

thanks and regards,
aditya.
Re: extract of Procedures [message #456737 is a reply to message #456733] Wed, 19 May 2010 06:53 Go to previous messageGo to next message
ramoradba
Messages: 2453
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Go ahead ..
What stopping you?

sriram Smile
Re: extract of Procedures [message #456754 is a reply to message #456733] Wed, 19 May 2010 07:42 Go to previous message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Use dbms_metadata and generate individual statements with specific spool filename. Something like this.
magvivek@Kaapi#cat something
sqlplus -s $DBA/$DBP@test <<EOF
set long 500000000;
set linesize 1000
set feed off;
SET HEAD off;
set trimspool on;
column xxx  format a400
-- for single spool
-- select DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name) as xxx from user_objects u where object_type='PROCEDURE' and object_name in ('P1','P2');
spool myList.sql
select 'spool '||u.object_name||'.sql'||CHr(10)|| 
	'select DBMS_METADATA.GET_DDL(''PROCEDURE'','''||u.object_name||''') from dual;'   ||chr(10)||
	'spool off;'
from user_objects u where object_type='PROCEDURE' and object_name in ('P1','P2');
spool off;
@myList.sql
exit;
EOF
magvivek@Kaapi#./something

spool P1.sql
select DBMS_METADATA.GET_DDL('PROCEDURE','P1') from dual;
spool off;

spool P2.sql
select DBMS_METADATA.GET_DDL('PROCEDURE','P2') from dual;
spool off;



  CREATE OR REPLACE PROCEDURE "DBADMIN"."P1"
as
begin
Null;
end;




  CREATE OR REPLACE PROCEDURE "DBADMIN"."P2"
as
begin
dbms_output.put_line('hello');
end;


magvivek@Kaapi#cat P1.sql


  CREATE OR REPLACE PROCEDURE "DBADMIN"."P1"
as
begin
Null;
end;


magvivek@Kaapi#cat P2.sql


  CREATE OR REPLACE PROCEDURE "DBADMIN"."P2"
as
begin
dbms_output.put_line('hello');
end;


magvivek@Kaapi#
Previous Topic: Archive log and hot backup
Next Topic: oracle 10g + ASM + block change tracking
Goto Forum:
  


Current Time: Sat Oct 25 03:58:57 CDT 2014

Total time taken to generate the page: 0.07639 seconds