Skip to Main Content

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.

Comments
Post Details
Added on Oct 8 2018
4 comments
12,690 views