用sql语句导出oracle中的存储过程和函数
标签：
用sql语句导出oracle中的存储过程和函数
echo off ;
SET heading off ;
SET feedback off ;
SPOOL ‘C:/PRC.SQL‘ replace
SELECT CASE
WHEN LINE = 1 THEN
‘CREATE OR REPLACE ‘ || TEXT
WHEN LINE = MAX_LINE THEN
TEXT || CHR(10 ) || ‘/‘
ELSE
TEXT
END
FROM USER_SOURCE A
LEFT JOIN (SELECT A.NAME,A.TYPE, MAX(LINE) MAX_LINE
FROM USER_SOURCE A
WHERE TYPE in ( ‘PROCEDURE‘, ‘PACKAGE‘ ,‘PACKAGE BODY‘ )
GROUP BY A.NAME,A.TYPE ) B ON A.NAME||A.TYPE = B.NAME||B.TYPE
WHERE A.TYPE in (‘PROCEDURE‘ ,‘FUNCTION‘, ‘PACKAGE‘, ‘PACKAGE BODY‘ ) AND A.NAME IN
(
SELECT name FROM ALL_SOURCE
WHERE (TYPE=‘PROCEDURE‘ or type=‘FUNCTION‘)
AND OWNER=‘你的应用实例‘
GROUP BY name
)
ORDER BY a.NAME||a.TYPE , LINE;
SPOOL OFF