通过存储过程批量生成spool语句
过存储过程批量生成spool语句
1 CREATE OR REPLACE PROCEDURE pro_yx_full_txt IS 2 export_handle UTL_FILE.file_type; 3 v_file nvarchar2(2000); 4 BEGIN 5 export_handle := UTL_FILE.FOPEN('MY_DIR', 'test.txt', 'w'); 6 UTL_FILE.PUT_LINE(export_handle,'set colsep '' 7 set trimspool on 8 set linesize 120 9 set pagesize 2000 10 set newpage 1 11 set heading off 12 set term off 13 14 '); 15 16 for vtable in (select table_name from user_tables) 17 loop 18 v_file :='spool D:\MY_DIR\'||vtable.table_name||'.txt'||' 19 select * from '||vtable.table_name||'; 20 spool off 21 / '; 22 UTL_FILE.PUT_LINE(export_handle,v_file); 23 end loop; 24 UTL_FILE.FCLOSE(export_handle); 25 EXCEPTION 26 WHEN OTHERS THEN 27 DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 2000)); 28 29 end;