用sql语句导出oracle中的存储过程和函数
时间：2021-07-01 10:21:17
帮助过：2人阅读
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
用sql语句导出oracle中的存储过程和函数
标签：
