title: Export query result to csv in oracle stored procedure
body: <p>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:</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>ID,ROLL_NO,RANK, 
  1,123456,1620,
  2,987654,1344,
  </code></pre>
  
  <p>Now herein lies my issue. As you can see each row within the output file is ending with an extra trailing <code>,</code>. 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:</p>
  
  <pre><code>ID,ROLL_NO,RANK 
  1,123456,1620
  2,987654,1344
  </code></pre>
  
  <p>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.</p>