8 Replies Latest reply: Nov 11, 2011 3:00 AM by Marwim RSS

    PL/SQL output to .CSV file

    592938
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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