CREATE OR REPLACE PROCEDURE PROC_OUTPUTDDL(pTYPE VARCHAR2,pNAME VARCHAR2,PMESSAGE VARCHAR2,POWNER VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER --调用者权限
AS
/*
功能:输出存储、表、函数等对象的创建语句到文件
*/
V_FILE UTL_FILE.FILE_TYPE;
V_OWNER VARCHAR2(100);

BEGIN
--判断是否有确定用户,缺省为当前用户
IF POWNER IS NULL THEN
 SELECT USER INTO V_OWNER FROM DUAL;
 ELSE
 V_OWNER:=POWNER;
 END IF;
--如果文件存在就追加内容,不存在就创建
IF DBMS_LOB.FILEEXISTS(BFILENAME('DDIR','DDL'||TO_CHAR(SYSDATE,'YYYY_MM_DD')||'.LOG'))=1
 THEN
V_FILE:=UTL_FILE.FOPEN('DDIR','DDL'||TO_CHAR(SYSDATE,'YYYY_MM_DD')||'.LOG','A');
ELSE
V_FILE:=UTL_FILE.FOPEN('DDIR','DDL'||TO_CHAR(SYSDATE,'YYYY_MM_DD')||'.LOG','W');
END IF;
UTL_FILE.NEW_LINE(V_FILE);
UTL_FILE.PUT_LINE(V_FILE,'***********STARTTIME['||TO_CHAR(SYSDATE,'YYYY_MM_DD HH24:MI:SS')||']********************');
UTL_FILE.PUT_LINE(V_FILE,PMESSAGE);
UTL_FILE.PUT_LINE(V_FILE,pTYPE||' '||pNAME||'的DDL语句为:');
for x in (SELECT DBMS_METADATA.GET_DDL(pTYPE,pNAME,V_OWNER) A FROM DUAL) LOOP
UTL_FILE.PUT_LINE(V_FILE,X.A);
END LOOP;

UTL_FILE.PUT_LINE(V_FILE,'*************ENDTIME['||TO_CHAR(SYSDATE,'YYYY_MM_DD HH24:MI:SS')||']********************');
UTL_FILE.FCLOSE(V_FILE);

EXCEPTION
WHEN OTHERS THEN
UTL_FILE.NEW_LINE(V_FILE);
UTL_FILE.PUT_LINE(V_FILE,FN_GETNAME||'出现错误：'||SQLCODE||'----'||SQLERRM);
UTL_FILE.FCLOSE(V_FILE);

END PROC_OUTPUTDDL;