Location: The Official Oracle Wiki

Discussion: How to back up proceduresReported This is a featured thread

Category: Discussion Forum / General Discussion
Showing 6 posts

gokhan.kucuk
How to back up procedures
Jul 2 2009, 5:11 AM EDT | Post edited: Jul 2 2009, 5:11 AM EDT
Hi to all,

I'm writing a backup utility for ORACLE to backup my procedures, but i get in stuck abour how to get ddl script of a procedure, do you have any idea about it ?

Actually i am able to get the source of a procedure from "all_source" table with selecting with type, but i need the script which is starting with 'CREATE OR REPLACE PROCEDURE.....'

Any Idea ?

1  out of 1 found this valuable. Do you?    
Keyword tags: back up procedure
chiaowen
chiaowen
1. RE: How to back up procedures
Jul 8 2009, 10:45 AM EDT | Post edited: Jul 8 2009, 10:45 AM EDT
Some GUI tool can generate those complete SQL, ie TOAD, PL/SQL developer and GridControl's Change Management (Dictionary Baseline).

or try to use DBMS_METADATA.GET_DDL('PACKAGE','package_name','owner'); 1  out of 1 found this valuable. Do you?    
ankitanshu
ankitanshu
2. RE: How to back up procedures
Jul 8 2009, 10:53 AM EDT | Post edited: Jul 8 2009, 10:53 AM EDT
"Hi to all,

I'm writing a backup utility for ORACLE to backup my procedures, but i get in stuck abour how to get ddl script of a procedure, do you have any idea about it ?

Actually i am able to get the source of a procedure from "all_source" table with selecting with type, but i need the script which is starting with 'CREATE OR REPLACE PROCEDURE.....'

Any Idea ?

"
Depends in what form you want to keep the backup...The best place is to get DDL in form of a file, (can be done on UNIX as well as WIN)
If you want to use all_source dictionary, then you can make use of shell script/Perl/sed to edit them as per ur requirements. The same thing in windows in achieved through batch scripts 1  out of 1 found this valuable. Do you?    
luciofer
luciofer
3. RE: How to back up procedures
Jul 9 2009, 4:05 PM EDT | Post edited: Jul 9 2009, 4:05 PM EDT
"Hi to all,

I'm writing a backup utility for ORACLE to backup my procedures, but i get in stuck abour how to get ddl script of a procedure, do you have any idea about it ?

Actually i am able to get the source of a procedure from "all_source" table with selecting with type, but i need the script which is starting with 'CREATE OR REPLACE PROCEDURE.....'

Any Idea ?

"
Actually the best way to backup your objects in Oracle is using EXPORT. It's easer to batch, and safer.
You can dump an schema or the whole db. Check the documentation for the exp command.

Cheers 1  out of 1 found this valuable. Do you?    
YeshwanthBhavane
YeshwanthBhavane
4. RE: How to back up procedures
Aug 5 2009, 5:41 AM EDT | Post edited: Aug 5 2009, 5:41 AM EDT
This script shud give u an basic idea of how to do the things with Oracle metadata. U can create ur own SQL script similar to below script to create a spool file with all the procedures.

set pages 500
set lines 300
set long 10000000
set define off
set sqlblank on
set echo on
select dbms_metadata.get_ddl('PROCEDURE',object_name) from dba_objects where object_type='PROCEDURE' and owner='HR';
 1  out of 1 found this valuable. Do you?    

gokhan.kucuk
5. RE: How to back up procedures
Aug 5 2009, 9:31 AM EDT | Post edited: Aug 5 2009, 9:31 AM EDT
Thank you Yeshwanth, this was what I was looking for... 1  out of 1 found this valuable. Do you?