Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

GENERATING CSV FILE WITH HEADERS USING STORED PROCEDURE

AB115Oct 8 2018 — edited Oct 8 2018

Hello Experts,

We need to generate .csv file with column headers using stored procedure. we are able to fetch the data but headers are not coming while generating .csv file.

Table:

CREATE TABLE emp_test

(

   empno       NUMBER,

   emp_name    VARCHAR2 (50),

   dept_name   VARCHAR2 (50),

   salary      NUMBER

)

/

Stored Procedure:

CREATE OR REPLACE PROCEDURE emp_prc

AS

      v_file                  UTL_FILE.FILE_TYPE;

BEGIN

   v_file :=

      SYS.UTL_FILE.FOPEN (location       => 'XXC6705_ERROR',

                          filename       => 'data_100818.csv',

                          open_mode      => 'w',

                          max_linesize   => 32767);

   LOOP

      FOR c_data IN (SELECT DISTINCT empno,

                                     emp_name,

                                     dept_name,

                                     salary

                       FROM emp_test)

      LOOP

         SYS.UTL_FILE.PUT_LINE (

            v_file,

               c_data.empno

            || ','

            || c_data.emp_name

            || ','

            || c_data.dept_name

            || ','

            || c_data.salary);

      END LOOP;

   END LOOP;

   SYS.UTL_FILE.FCLOSE (v_file);

END emp_prc;

/

please suggest.

Thanks.

This post has been answered by BluShadow on Oct 8 2018
Jump to Answer
Comments
Post Details
Added on Oct 8 2018
4 comments
17,067 views