Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

export stored procedures in .sql file

MoazzamFeb 7 2011 — edited Feb 8 2011
I am using Oracle 10g R2. I want to write a script to generate a .sql file containing all stored procedures and functions present in my database. The purpose is to take backup of these objects.

i am trying to use the following code:
BEGIN
  for c in (select OBJECT_NAME,OBJECT_TYPE from user_objects o where o.object_type IN ( 'PROCEDURE','FUNCTION'))
  loop
    execute immediate ( 'SELECT dbms_metadata.get_ddl(''' || c.object_type || ''',''' || OBJECT_NAME || ''') FROM dual;');
  end loop;
  
END;
But i want to know how can i export the definition of objects in .sql file using this script.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 8 2011
Added on Feb 7 2011
12 comments
25,450 views