Oracle 实现将查询结果保存到文本txt中

spool j:\test.txt

select sysdate from dual;

spool off ;

CREATE OR REPLACE PROCEDURE PROC_INSERTSQLTXT AS

  V_FILE UTL_FILE.FILE_TYPE;

  V_INPUT CLOB;

BEGIN

  V_FILE := UTL_FILE.FOPEN( 'DATA_PUMP_DIR' , 'EMPNO.TXT' , 'W' );

  FOR I IN ( SELECT EMPNO|| '|' ||

      ENAME|| '|' ||

      JOB || '|' ||

      MGR || '|' ||

      to_char(HIREDATE, 'yyyy-mm-dd' ) || '|' ||

      SAL || '|' ||

      COMM || '|' ||

      DEPTNO as stringname FROM SCOTT.EMP)

  LOOP

  UTL_FILE.PUT_LINE(V_FILE,I.STRINGNAME);

  END LOOP;

  UTL_FILE.FCLOSE(V_FILE);

EXCEPTION

  WHEN UTL_FILE.ACCESS_DENIED THEN

  DBMS_OUTPUT.PUT_LINE( '拒绝访问!' );

  WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE( 'SQLERRM: ' || SQLERRM);

END ;