1. 生成sql脚本，SQL plus执行，类型名称、用户名需大写
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LINESIZE 10000
SET LONG 90000
SET FEEDBACK OFF
SET FEED OFF
SET ECHO OFF
-- 指定文件路径，文件夹不能为空
SPOOL E:\ytzz\copy\czbdc_dp\schema_bdc_workflow.sql
SELECT CASE
WHEN U.OBJECT_TYPE IN (‘PROCEDURE‘, ‘FUNCTION‘ ， ‘PACKAGE‘, ‘TRIGGER‘) THEN
DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER) ||
CHR(10) || ‘/‘
ELSE
DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER)||
CHR(10) || ‘;‘
END AS SCOTT_DDL
FROM DBA_OBJECTS U
WHERE U.OBJECT_TYPE IN (‘PROCEDURE‘, ‘FUNCTION‘, ‘PACKAGE‘, ‘TRIGGER‘)
AND U.OWNER=‘TEST‘;
SPOOL OFF;
2. 查询
-- 根据用户对象名和对象类型，查找对面内容 存储过程、函数、触发器 -- PROCEDURE TRIGGER FUNCTION PACKAGE
SELECT DBMS_METADATA.GET_DDL(‘PROCEDURE‘, U.OBJECT_NAME,‘BDCK‘)
-- 任意用户登录都可以
from dba_objects U
where u.OBJECT_TYPE in (‘PROCEDURE‘) and u.owner = ‘BDCK‘
;
--查触发器内容 -- PROCEDURE TRIGGER FUNCTION
SELECT DBMS_METADATA.GET_DDL(‘PROCEDURE‘, U.OBJECT_NAME)
-- 当前登录用户
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = ‘FUNCTION‘
;
函数使用参数说明
SQL> DESC DBMS_METADATA.GET_DDL
PARAMETER TYPE MODE DEFAULT?
----------- -------- ---- --------
(RESULT) CLOB
OBJECT_TYPE VARCHAR2 IN
NAME VARCHAR2 IN
SCHEMA VARCHAR2 IN Y
VERSION VARCHAR2 IN Y
MODEL VARCHAR2 IN Y
TRANSFORM VARCHAR2 IN Y
补充 把clob转换为字符串
SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL(‘FUNCTION‘, ‘FUNC_NAME‘, ‘SCHEMA‘)) as 内容 FROM dual;