Home » RDBMS Server » Server Utilities » Export only Procedures as *.proc file (11.2.0.2.0 - 64bit, Windows)
Export only Procedures as *.proc file [message #563838] Fri, 17 August 2012 07:24 Go to next message
oraranjangmailcom
Messages: 65
Registered: April 2010
Member
Hi,

I have to copy all the procedures in database to local folder and extension( or file type) of each procedure is ".proc".

I did tried with dbms_metadata but as there are 300 procedures it consuming time & I want separate file for each one.

select
   dbms_metadata.GET_DDL('PROCEDURE',u.object_name), u.object_name
from
   user_objects u
where
   object_type = 'PROCEDURE'
   AND object_name in( 'P1');


Can I have some help on this.


Regards,
Ranjan
Re: Export only Procedures as *.proc file [message #563856 is a reply to message #563838] Fri, 17 August 2012 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use dba_source instead but I bet you already have done it with dbms_metadata as I doubt your query last 3 hours.

Regards
Michel
Re: Export only Procedures as *.proc file [message #565211 is a reply to message #563838] Fri, 31 August 2012 10:55 Go to previous message
LKBrwn_DBA
Messages: 432
Registered: July 2003
Location: WPB, FL
Senior Member
You could try something like this:
SET LIN 512 TRIMS ON PAGES 0 LONG 32000 LONGC 120
SPO gen_proc_ddl.sql

SELECT
   'spo cr_' || owner || '_' || object_name || '.pls' ||CHR (10)||
   'select dbms_metadata.GET_DDL(''PROCEDURE'',''' ||object_name ||''',''' 
        || owner ||''')||chr(10||''/'' from dual;' ||CHR (10)||
   'spo off'
    FROM dba_procedures
   WHERE owner = 'MYSCHEMA'
     AND object_type = 'PROCEDURE'
ORDER BY owner, object_name
/

SPO OFF
SET LIN 256 TRIMS ON PAGES 0 LONG 32000 LONGC 120
SET VER OFF FEED OFF TERM OFF ECHO OFF
@gen_proc_ddl

PS: The ".pls" suffix is closer to standard practice for naming PL/SQL procedures.

[Updated on: Fri, 31 August 2012 11:04] by Moderator

Report message to a moderator

Previous Topic: SQL*LOADER (2 Merged)
Next Topic: ORA: 31633 unable to create master table :SYS.SYS_EXPORT_SCHEMA_05"
Goto Forum:
  


Current Time: Tue Sep 16 00:04:35 CDT 2014

Total time taken to generate the page: 0.09182 seconds