Export query result to csv in oracle stored procedure

Question

So i have a query that i would like to execute through a stored procedure and export the output of the query to a CSV file. So i am using the following stored procedure to do it:

CREATE OR REPLACE PROCEDURE parseCSV(
p_file_dir         VARCHAR2, -- Oracle directory name
p_file_name     VARCHAR2, -- filename
p_sql_query        VARCHAR2, -- select * from table or some such query
p_delimiter     CHAR      -- column delimiter
)
AS

l_cursor_handle  INTEGER;
l_dummy              NUMBER;
l_col_cnt          INTEGER;
l_rec_tab            DBMS_SQL.DESC_TAB;
l_current_col      NUMBER(16);
l_current_line   VARCHAR2(2047);
l_column_value   VARCHAR2(300);
l_file_handle      UTL_FILE.FILE_TYPE;
l_print_text       VARCHAR2(100);
l_record_count   NUMBER(16) := 0;

BEGIN
   l_file_handle := UTL_FILE.FOPEN(p_file_dir, p_file_name, 'a', 2047); 
   l_cursor_handle := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(l_cursor_handle, p_sql_query, DBMS_SQL.native);
   l_dummy := DBMS_SQL.EXECUTE(l_cursor_handle);
   DBMS_SQL.DESCRIBE_COLUMNS(l_cursor_handle, l_col_cnt, l_rec_tab); 
   l_current_col := l_rec_tab.FIRST;
   IF (l_current_col IS NOT NULL) THEN
      LOOP
         DBMS_SQL.DEFINE_COLUMN(l_cursor_handle, l_current_col, l_column_value, 300);
         l_print_text := l_rec_tab(l_current_col).col_name || p_delimiter;
         UTL_FILE.PUT (l_file_handle, l_print_text);
         l_current_col := l_rec_tab.NEXT(l_current_col);
         EXIT WHEN (l_current_col IS NULL);
      END LOOP;
   END IF;
   UTL_FILE.PUT_LINE (l_file_handle,' ');
   LOOP
      EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cursor_handle) = 0; 

      l_current_line := '';
      FOR l_current_col IN 1..l_col_cnt LOOP
         DBMS_SQL.COLUMN_VALUE (l_cursor_handle, l_current_col, l_column_value);
         l_print_text := l_column_value || p_delimiter;

         l_current_line := l_current_line || l_column_value || p_delimiter;
      END LOOP;
      l_record_count := l_record_count + 1;
      UTL_FILE.PUT_LINE (l_file_handle, l_current_line);
   END LOOP;
   UTL_FILE.FCLOSE (l_file_handle);
   DBMS_SQL.CLOSE_CURSOR(l_cursor_handle);
END;
/

The procedure when executed processes the query and then stores the result into a delimited file. For example, the output of the procedure for a regular SELECT statement will be of this form:

ID,ROLL_NO,RANK, 
1,123456,1620,
2,987654,1344,

Now herein lies my issue. As you can see each row within the output file is ending with an extra trailing ,. Now, due to my lack of knowledge in plsql, i can't think of a modification that i can do to the procedure so that the expected output file would be of this form:

ID,ROLL_NO,RANK 
1,123456,1620
2,987654,1344

Could someone be kind enough to help out an Oracle newbie here and give me some pointers as to how i can do it? I would appreciate it a lot.

user_3049419 · Answer

First, create a directory in the database and provide the read, write access to that directory.

To create a directory:

CREATE OR REPLACE DIRECTORY alias AS 'pathname';

To Grant Read,Write:

GRANT read,write ON DIRECTORY alias TO {user | role | PUBLIC};

After that use below stored procedure to get the output of SQL query in any file format.

CREATE OR REPLACE PROCEDURE CSV_EXPORT AS
  CURSOR c_data IS
    SELECT * from table_name;

  v_file  UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN(location     => 'FILES1',
                           filename     => 'csv_exp.txt',
                           open_mode    => 'w',
                           max_linesize => 32767);
  FOR cur_rec IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file,
                      cur_rec.column1    || ',' ||
                      cur_rec.column2 );
  END LOOP;
  UTL_FILE.FCLOSE(v_file);

EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE;
END;

To run the stored procedure:

EXEC CSV_EXPORT;

here in code FILES1 is directory name.

Export query result to csv in oracle stored procedure

6 Answers