-- C:/dba必需是已经存在的目录
-- create or replace directory UTL_DIR as 'C:\dba';
--用sys用户登录给要访问的用户指定访问目录的权限
grant write on directory UTL_DIR to public;
grant read on directory UTL_DIR to public;
//----------------------------- 声明部分 -------------------------------
create or replace package P_EXPORTDLL is
PROCEDURE Export_Table_Design(P_TABLE_NAME VARCHAR2, P_FILENAME VARCHAR2);
end P_EXPORTDLL;
//----------------------------- body部分部分 -------------------------------
create or replace package body P_EXPORTDLL is
PROCEDURE Export_Table_Design(P_TABLE_NAME VARCHAR2, P_FILENAME VARCHAR2)IS
BEGIN
DECLARE
L_FILE UTL_FILE.FILE_TYPE;
L_BUFFER VARCHAR2(1000);
L_AMOUNT BINARY_INTEGER := 100;
L_POS INTEGER := 1;
L_CLOB CLOB;
L_CLOB_LEN INTEGER;
--P_TABLE_NAME := 'TEST_USER';
-- P_FILENAME :='TEST_USER';
BEGIN
SELECT DBMS_METADATA.GET_DDL('TABLE', P_TABLE_NAME) || ';'
INTO L_CLOB
FROM DUAL;
L_CLOB_LEN := DBMS_LOB.GETLENGTH(L_CLOB);
L_FILE := UTL_FILE.FOPEN('UTL_DIR', P_FILENAME || '.sql', 'a', 1000);
WHILE L_POS < L_CLOB_LEN LOOP
DBMS_LOB.READ(L_CLOB, L_AMOUNT, L_POS, L_BUFFER);
UTL_FILE.PUT(L_FILE, L_BUFFER);
L_POS := L_POS + L_AMOUNT;
END LOOP;
UTL_FILE.FCLOSE(L_FILE);
END;
END Export_Table_Design;
end P_EXPORTDLL;