Create CSV File from table

CREATE OR REPLACE PROCEDURE CREATE_CSV (p_query IN VARCHAR2)
AS
   l_cur              PLS_INTEGER;
   l_cur_rows         PLS_INTEGER;
   l_column_cnt       PLS_INTEGER;
   l_desc_tab         DBMS_SQL.desc_tab;
   l_buffer           VARCHAR2 (32767);
   l_file             UTL_FILE.file_type;
   l_string           VARCHAR2 (15);


   g_record_cnt       NUMBER := 0;
   g_directory_path   VARCHAR2 (500) := '/usr/tmp'; -- path must be present in utl_file_dir of v$parameter
   g_file_name        VARCHAR2 (250) := 'ra_terms';
   g_seperator        VARCHAR2 (5) := ',';
BEGIN
   g_record_cnt := 0;

   DBMS_OUTPUT.PUT_LINE (' Start writing in CSV file :' || g_file_name);

   l_cur := DBMS_SQL.open_cursor;
   DBMS_SQL.parse (l_cur, p_query, DBMS_SQL.native);
   DBMS_SQL.describe_columns (l_cur, l_column_cnt, l_desc_tab);

   FOR i IN 1 .. l_column_cnt
   LOOP
      DBMS_SQL.define_column (l_cur,
                              i,
                              l_buffer,
                              32767);
   END LOOP;

   l_cur_rows := DBMS_SQL.EXECUTE (l_cur);
   l_file :=
      UTL_FILE.fopen (g_directory_path,
                      g_file_name || '.csv',
                      'w',
                      32767);

   FOR i IN 1 .. l_column_cnt
   LOOP
      IF i > 1
      THEN
         UTL_FILE.put (l_file, g_seperator);
      END IF;

      UTL_FILE.put (l_file, l_desc_tab (i).col_name);
   END LOOP;

   UTL_FILE.new_line (l_file);

   LOOP
      EXIT WHEN DBMS_SQL.fetch_rows (l_cur) = 0;

      FOR i IN 1 .. l_column_cnt
      LOOP
         IF i > 1
         THEN
            UTL_FILE.put (l_file, g_seperator);
         END IF;

         DBMS_SQL.COLUMN_VALUE (l_cur, i, l_buffer);

         IF INSTR (l_buffer, '"') != 0
         THEN
            SELECT REPLACE (l_buffer, '"', '""') INTO l_buffer FROM DUAL;
         END IF;

         IF INSTR (l_buffer, ',') != 0
         THEN
            l_string := '"';
            l_buffer := l_string || l_buffer || l_string;
         ELSE
            l_string := '';
         END IF;

         UTL_FILE.put (l_file, l_buffer);
      END LOOP;

      g_record_cnt := g_record_cnt + 1;
      UTL_FILE.new_line (l_file);
   END LOOP;

   DBMS_OUTPUT.PUT_LINE (
      ' Number Of rows inserted in CSV : ' || g_record_cnt);

   UTL_FILE.fclose (l_file);

   DBMS_OUTPUT.PUT_LINE (' End procedure');
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;

      IF DBMS_SQL.is_open (l_cur)
      THEN
         DBMS_SQL.close_cursor (l_cur);
      END IF;

      DBMS_OUTPUT.PUT_LINE (
         'Unexpected Error : ' || SUBSTR (SQLERRM, 1, 250));
END CREATE_CSV;

--------------------------------------------------------------------------------------------------

-- Call above procedure to generate CSV file data from ra_terms table

DECLARE
BEGIN
   create_csv ('SELECT * FROM ra_terms');
END;