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;