Dynamically sending CSV File from Oracle to a Network Location with Headers

Question

I have a business requirement that is looking for the ability to have CSV files placed in a network location. I am using a procedure which uses a cursor to send the data to the required location. But I want to include the column headers along with the data. How can I achieve that?

create or replace PROCEDURE    REPORT_FILE (
   p_status    OUT NUMBER,
   p_message   OUT VARCHAR2)

IS

   file_loc          CONSTANT VARCHAR2(64)   := 'New_DIR';

    fid_out                 UTL_FILE.FILE_TYPE;
    file_out                VARCHAR2(58);
    file_out_ext            VARCHAR2(4):='.DAT';

    tmp                     VARCHAR2(999);

       CURSOR c_results is


         Select
              First_name,Last_name,Phone_number from customer
         ;
BEGIN
   DBMS_OUTPUT.enable (1000000);
   DBMS_OUTPUT.put_line (
      'START TIME IS ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

insert into customer (select * from customer_data);
   

     ----- output filename
        file_out := 'New_data' || TO_CHAR(SYSDATE,cs_datefmt)
                 || TO_CHAR(SYSDATE,cs_timefmt) ||'.CSV' ;

         user_utility.print_line('current quater filename is : ' || file_out );
        
        fid_out:=UTL_FILE.FOPEN(file_loc,file_out,'W');
            
            FOR  cur_rec in c_results  LOOP

                              
                   tmp := tmp || '"' || cur_rec .first_name|| '"|';
                   tmp := tmp || '"' || cur_rec .Last_name || '"|';
                   tmp := tmp || '"' || cur_rec .Phone_number|| '"|';


             UTL_FILE.PUT_LINE(fid_out,tmp);


            END LOOP;
    user_utility.print_line ('REPORT completed successfully .. ');

    COMMIT;

    p_status := 0;
         user_utility.update_job (loc_prog_name, loc_job_id,'C', sysname);

EXCEPTION
   WHEN OTHERS
   THEN
      loc_errcode := SQLCODE;
      loc_errmess := SQLERRM;

      user_utility.update_job (loc_prog_name,
                               loc_job_id,
                               'I',
                               sysname);

      user_utility.error_handler (loc_job_id,
                                  loc_prog_name,
                                  loc_prog_step,
                                  loc_operation,
                                  loc_table_name,
                                  loc_errcode,
                                  loc_errmess,
                                  loc_note);
      p_status :=  loc_errcode;
      p_message := 'REPORT has failed. '|| loc_errmess;

      rollback;

      DBMS_OUTPUT.put_line ('Error code:' || loc_errcode);
      DBMS_OUTPUT.put_line ('Error message:' || loc_errmess);
      DBMS_OUTPUT.put_line (
         'END TIME IS ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

END;

The csv file is sent to the location but without column headers. I want the column headers as well to be sent.

Is the question about accessing the network location or printing the column headers? — William Robertson
– William Robertson, Commented Oct 29, 2022 at 12:49

William Robertson · Accepted Answer · 2022-10-29 13:21:23Z

1

To extract the column headers from a ref cursor, use dbms_sql.describe_columns. However, parsing the cursor through dbms_sql will consume the cursor, so you might as well use it for the whole job.

Open a ref cursor.
Use dbms_sql.to_cursor_number to ingest it into dbms_sql.
Call dbms_sql.describe_columns to get back an array containing the column headers and their data types. You can loop through this to build your header.
Call dbms_sql.fetch_rows in a loop to retrieve each row, and dbms_sql.column_value to retrieve each value within the row.
Close the cursor with dbms_sql.close_cursor.

This gives you a generic method to make a CSV-format extract from any arbitrary ref cursor.

The full code is a bit long to post here, but have a look at www.williamrobertson.net/documents/refcursor-to-csv.shtml.

answered Oct 29, 2022 at 13:21

William Robertson

16.1k4 gold badges41 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamically sending CSV File from Oracle to a Network Location with Headers

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related