-- 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;