Oracle 使用pl/sql将表中的数据读出到文件中

（1）在服务器上创建文件路径及文件

[oracle@redhat errormsg]$ touch test01.txt

（2）在数据库中创建路径及授权

（3）创建存储过程

CREATE OR REPLACE PROCEDURE export_test01 IS

export_handle UTL_FILE.file_type;

BEGIN

export_handle := UTL_FILE.FOPEN('LIJIAMAN_DIR', 'test01.txt', 'w');

FOR x IN (SELECT * FROM lijiaman.test01) LOOP

  UTL_FILE.PUT_LINE(export_handle,x.id || ',' || x.name);

END LOOP;

UTL_FILE.FCLOSE(export_handle);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 2000));

END;

（4）创建job，1分钟跑一次

declare

    job1 number;

begin

    sys.dbms_job.submit(job1,'export_test01;',next_date => sysdate,interval =>'sysdate + 1/1440');

end;

（5）观察job情况

SQL> select * from user_jobs;

       JOB LOG_USER    PRIV_USER   SCHEMA_USER   LAST_DATE   LAST_SEC THIS_DATE   THIS_SEC NEXT_DATE   NEXT_SEC TOTAL_TIME BROKEN INTERVAL            FAILURES   WHAT

---------- ----------- ----------- ------------- ----------- -------- ----------- -------- ----------- -------- ---------- ------ ------------------  ---------- -----------------

         4 LIJIAMAN    LIJIAMAN    LIJIAMAN      2017/11/22  23:24:32                      2017/11/22  23:25:32          0 N      sysdate + 1/1440    0          export_test01;