Skip to Main Content
Forums

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Saubhik

Out side the FOR loop add another UTL_FILE.PUT_LINE with your column name. Look at this example:

  By Blushadow

But, if this is one time activity then SQL Developer can do this easily: SELECT /*csv*/ * FROM scott.emp;

EDIT: Just looking at your program second time, Be careful of infinite loop! Look at the example I have given carefully.

1 person found this helpful
AB115

thanks for the pointer but just wanted to know that if don't want to pass p_header_file as input parameter and without that need to display headers. Is there any approach to handle?

please suggest.

BluShadow
Answer

Of course there's an approach, you just adapt the code to do what you want (i.e. put in some effort)...

As SYS user:

CREATE OR REPLACE DIRECTORY TEST_DIR AS '\tmp\myfiles'
/
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
/

As user:

CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2
                                     ,p_dir IN VARCHAR2
                                     ,p_file IN VARCHAR2
                                     ) IS
  v_finaltxt  VARCHAR2(4000);
  v_v_val     VARCHAR2(4000);
  v_n_val     NUMBER;
  v_d_val     DATE;
  v_ret       NUMBER;
  c           NUMBER;
  d           NUMBER;
  col_cnt     INTEGER;
  f           BOOLEAN;
  rec_tab     DBMS_SQL.DESC_TAB;
  col_num     NUMBER;
  v_fh        UTL_FILE.FILE_TYPE;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
  d := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
  FOR j in 1..col_cnt
  LOOP
    CASE rec_tab(j).col_type
      WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
      WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
      WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
    ELSE
      DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
    END CASE;
  END LOOP;
  -- This part outputs the HEADER
  v_fh := UTL_FILE.FOPEN(upper(p_dir),p_file,'w',32767);
  FOR j in 1..col_cnt
  LOOP
    v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
  END LOOP;
  UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
  -- This part outputs the DATA
  LOOP
    v_ret := DBMS_SQL.FETCH_ROWS(c);
    EXIT WHEN v_ret = 0;
    v_finaltxt := NULL;
    FOR j in 1..col_cnt
    LOOP
      CASE rec_tab(j).col_type
        WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                    v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
        WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                    v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
        WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                    v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
      ELSE
        DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
        v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
      END CASE;
    END LOOP;
  --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
    UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
  END LOOP;
  UTL_FILE.FCLOSE(v_fh);
  DBMS_SQL.CLOSE_CURSOR(c);
END;
/

Example:

SQL> exec run_query('select * from emp','TEST_DIR','output.csv');
 
PL/SQL procedure successfully completed.

Output.csv file contains:

empno,ename,job,mgr,hiredate,sal,comm,deptno
7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,,30
7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,,30
7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10

Adapt to output different datatypes and styles are required.

Marked as Answer by AB115 · Sep 27 2020
AB115

thanks..it got resolved with this approach.

1 - 4

Post Details

Added on Oct 8 2018
4 comments
16,719 views