Export only Procedures as *.proc file [message #563838] |
Fri, 17 August 2012 07:24 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
oraranjangmailcom
Messages: 67 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 #565211 is a reply to message #563838] |
Fri, 31 August 2012 10:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](/forum/images/custom_avatars/61988.jpg) |
LKBrwn_DBA
Messages: 487 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
|
|
|