oracle 备份建表sql

STEP1 :先在外部sql窗口执行:EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY OUTPUTDIR AS ''D:\''';  

STEP2:创建存储过程,然后执行~。

CREATE OR REPLACE PROCEDURE TABLE_TO_TXT2 IS

FILE_HANDLE UTL_FILE.FILE_TYPE;

CURSOR USER_TABLE_CSR IS

SELECT * FROM USER_TABLES;

ROW_USER_TABLE USER_TABLE_CSR %ROWTYPE;

TABLENAME VARCHAR2(200);

CREATE_TABLE_SQL_CLOB CLOB;

BEGIN

FOR ROW_USER_TABLE IN USER_TABLE_CSR LOOP

TABLENAME := ROW_USER_TABLE.TABLE_NAME;

DBMS_OUTPUT.PUT_LINE(TABLENAME);

FILE_HANDLE := UTL_FILE.FOPEN('OUTPUTDIR' , 'CREATE_TABLE_SQL.txt', 'a');

UTL_FILE.PUT_LINE(FILE_HANDLE, '');

SELECT DBMS_METADATA.GET_DDL('TABLE', tablename)

INTO CREATE_TABLE_SQL_CLOB

FROM DUAL;

UTL_FILE.PUT_LINE(FILE_HANDLE, CREATE_TABLE_SQL_CLOB);

UTL_FILE.FCLOSE(FILE_HANDLE);

END LOOP;

END;

评论

暂无评论