http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1992907061984


Here is what I would do (so we can maximize shared sql reuse -- a certain habit of mine).


create table dump_csv_parameters
( job_id int primary key,
  query  clob,
  separator varchar2(5),
  dir  varchar2(255),
  filename varchar2(255),
  last_time date,
  last_row_count number
);

we will use this table to store the parameters for a dump_csv call.  Also, for ease, we'll stuff the last time run and the last count of rows in here as well -- so we can easily verify row counts and times and such...Next, we'll create a small interface routine to read the parameters for a given job and run dump_csv:

create or replace procedure run_dump_csv( p_job_id in number )
as
    l_cnt number;
begin
  for x in ( select * from dump_csv_parameters where job_id = p_job_id )
  loop
     l_cnt := dump_csv(dbms_lob.substr(x.query,32000,1), x.separator, x.dir, x.filename);
     update dump_csv_parameters
        set last_time = sysdate, last_row_count = l_cnt
      where job_id = p_job_id;
  end loop;
end;
/


And now we can actually schedule the job itself.  Here we schedule every night at midnight.  Note how we pass the VARIABLE job -- not a VALUE for a job -- into the procedure (if you are interested in how this all works -- I have a chapter on it in my book showing how the job queues actually do their stuff).  When the job is run, it'll be passed its unique job ID.  If we schedual all jobs in our system this way -- we'll have exactly one query in the shared pool -- "begin run_dump_csv( JOB ); end;" -- instead ofbegin dump_csv( 'select * from t1', '|', '/tmp/', 't1.dat' ); end;begin dump_csv( 'select * from t2', '|', '/tmp/', 't2.dat' ); end;....


declare
   l_job  number;
begin
   dbms_job.submit( l_job,
                   'run_dump_csv( JOB );',
                    trunc(sysdate)+1,
                   'trunc(sysdate)+1' );

   insert into dump_csv_parameters
   (job_id, query,separator,dir,filename)
   values
   ( l_job, 'select * from emp', '|', '/tmp/', 'emp.dat' );

   commit;
end;
/

and when this job runs, it'll create the file and update the table for us -- every night at midnight....

http://nimishgarg.blogspot.co.uk/2011/09/create-csv-file-using-plsql.html

Very good and farst solution:
http://www.oracle-developer.net/display.php?id=425

SQL> DECLARE
  2
  3     v_file    CLOB;
  4     v_buffer  VARCHAR2(32767);
  5     v_name    VARCHAR2(128) := 'clob2file_buffered.txt';
  6     v_lines   PLS_INTEGER := 0;
  7     v_eol     VARCHAR2(2);
  8     v_eollen  PLS_INTEGER;
  9     c_maxline CONSTANT PLS_INTEGER := 32767;
 10
 11  BEGIN
 12
 13     v_eol := CASE
 14                 WHEN DBMS_UTILITY.PORT_STRING LIKE 'IBMPC%'
 15                 THEN CHR(13)||CHR(10)
 16                 ELSE CHR(10)
 17              END;
 18     v_eollen := LENGTH(v_eol);
 19
 20     DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
 21
 22     FOR r IN (SELECT x || ',' || y || ',' || z AS csv
 23               FROM   source_data)
 24     LOOP
 25
 26        IF LENGTH(v_buffer) + v_eollen + LENGTH(r.csv) <= c_maxline THEN
 27           v_buffer := v_buffer || v_eol || r.csv;
 28        ELSE
 29           IF v_buffer IS NOT NULL THEN
 30              DBMS_LOB.WRITEAPPEND(
 31                 v_file, LENGTH(v_buffer) + v_eollen, v_buffer || v_eol
 32                 );
 33           END IF;
 34           v_buffer := r.csv;
 35        END IF;
 36
 37        v_lines := v_lines + 1;
 38
 39     END LOOP;
 40
 41     IF LENGTH(v_buffer) > 0 THEN
 42        DBMS_LOB.WRITEAPPEND(
 43           v_file, LENGTH(v_buffer) + v_eollen, v_buffer || v_eol
 44           );
 45     END IF;
 46
 47     DBMS_XSLPROCESSOR.CLOB2FILE(v_file, 'DUMP_DIR', v_name);
 48     DBMS_LOB.FREETEMPORARY(v_file);
 49
 50     DBMS_OUTPUT.PUT_LINE('File='||v_name||'; Lines='||v_lines);
 51
 52  END;
 53  /
File=clob2file_buffered.txt; Lines=1000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:28.65