This discussion is archived
8 Replies Latest reply: Nov 11, 2011 1:00 AM by Marwim RSS

PL/SQL output to .CSV file

592938 Newbie
Currently Being Moderated
Hi Guys,

Using a PL/SQL procedure, does Oracle provide a useful operator to output the recordset as a .CSV file format.

I wish to output all the records from a table into a .CSV format and onto a server directory.

Any help would be greatfully received!
  • 1. Re: PL/SQL output to .CSV file
    NicloeiW Journeyer
    Currently Being Moderated
    you can use utl_file
    http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref14093
  • 2. Re: PL/SQL output to .CSV file
    592938 Newbie
    Currently Being Moderated
    Hi,
    Thanks for the quick response, but I am a little confused as to the use of this tool, and the documentation is not very reader-friendly.

    The example I have is:

    create or replace procedure utl_file_test_write
    (
    path in varchar2,
    filename in varchar2,
    firstline in varchar2,
    secondline in varchar2)
    is
    output_file utl_file.file_type;
    begin
    output_file := utl_file.fopen (path,filename, 'W');

    utl_file.put_line (output_file, firstline);
    utl_file.put_line (output_file, secondline);
    utl_file.fclose(output_file);

    end;
    /

    Lets say my table is called TableA and I wish to output Field1 and Field2 into a file called TableA.csv, and a path called C:\UCMI...where would I declare this information on the above example.

    Apologies but I have never used this tool.
  • 3. Re: PL/SQL output to .CSV file
    pl/sql novice Newbie
    Currently Being Moderated
    The way I know is to use a cursor for loop, and inside this for loop, generate your csv output line by line, manually inserting a ', ' in between the fields, then use utl_file.put_line to write the result to a text file. If I remember correctly, you'll need to open the file in append mode so subsequent writes in the for loop won't overwrite previous results (otherwise you'll only get one line).

    The method is very similar to C programming or even unix system programming, when you obtain a file handle, generate your output and write to the file handle, and finally close it.

    Steve F. has talked about how inefficient cursor for loop can be, but this is something simple I can learn in the beginning. The experts in this forum can point to a better, more efficient method for sure.
  • 4. Re: PL/SQL output to .CSV file
    450441 Journeyer
    Currently Being Moderated
    The way I know is to use a cursor for loop, and
    inside this for loop, generate your csv output line
    by line, manually inserting a ', ' in between the
    fields, then use utl_file.put_line to write the
    result to a text file.
    That's pretty much it, yeah.
    If I remember correctly,
    you'll need to open the file in append mode so
    subsequent writes in the for loop won't overwrite
    previous results (otherwise you'll only get one
    line).
    Only if you were opening and closing the file inside the loop, which would be a bit of a silly thing to do.

    As for the rest of it, the approved way of declaring the directory for output is to use

    CREATE DIRECTORY WIBBLE as 'C:\wibble';

    GRANT READ, WRITE on WIBBLE to myuser;

    then in code

    fhandle := utl_file.fopen('WIBBLE','output.csv','w');

    That c:\ worries me though - your output will be created on the database server not the client.
  • 5. Re: PL/SQL output to .CSV file
    592938 Newbie
    Currently Being Moderated
    FAO: PL/SQL Novice

    Do you have an example of your LOOP coding as I believe I am close but not close enough. I have added an extract from my program below but not sure how correct it is:

    BEGIN
    output_file := utl_file.fopen (path,filename, 'W');

    FOR r1 IN c_output LOOP
    utl_file.put_line(output_file,
         ||r1.O_FNAM||'|'
         ||r1.O_MNAM||'|'
    ||r1.O_SNAM||'|'
         ||r1.O_SEX||'|'
    );
    END LOOP;
         utl_file.fclose(output_file);
  • 6. Re: PL/SQL output to .CSV file
    pl/sql novice Newbie
    Currently Being Moderated
    You can find many useful links in asktom.oracle.com.
    Tom has written a lot of useful code in his website including CSV export.

    Another useful resource is PL/SQL Programming (4th edition) by Steve F.
    I actually base my design on his examples.

    Happy digging!
  • 7. Re: PL/SQL output to .CSV file
    899582 Newbie
    Currently Being Moderated
    I am trying to get the CSV file out from a cursor but getting an error .Please help me correct the code.

    Error(232,3): PL/SQL: Statement ignored
    Error(232,19): PLS-00221: 'PIO_CSTDN_EXTRACT_CUR' is not a procedure or is undefined

    _____________________________________________________________

    v_chr_sql := 'Select * from xxx';
    OPEN pio_cstdn_extract_cur FOR v_chr_sql;

    v_file := UTL_FILE.FOPEN(location => v_location ,
    filename => v_file_name,
    open_mode => 'w',
    max_linesize => 32767);
    FOR cur_rec IN pio_cstdn_extract_cur LOOP
    UTL_FILE.PUT_LINE(v_file,
    cur_rec.loan_id || ',' ||
    -- cur_rec.ename || ',' ||
    -- cur_rec.job || ',' ||
    -- cur_rec.mgr || ',' ||
    -- cur_rec.hiredate || ',' ||
    -- cur_rec.empno || ',' ||
    -- cur_rec.sal || ',' ||
    -- cur_rec.comm || ',' ||
    cur_rec.pool_id);
    END LOOP;
    UTL_FILE.FCLOSE(v_file);
    ___________________________________________________
  • 8. Re: PL/SQL output to .CSV file
    Marwim Expert
    Currently Being Moderated
    Hello 896579,

    you might want to read the FAQ before posting, especially {message:id=9360002}

    - please don't use a almost 3 year old thread to ask a question. Open a new one instead.

    - use
     tags to format your example
    
    - this is not the complete code. Where is the cursor defined?
    
    Regards
    Marcus