Anonymous PL/SQL to write a CSV from SQL

/*
csv_experiment.sql

Created by Katie Kodes as an edit of Tim Hall's work on October 5, 2020.

Helps generate CSV files from various SQL queries.

Please do not summon put(), new_line(), or generate_all() -- they are helper procedures.

Instead, please summon generate().
(Or generate_rc(), or output() for system output instead of file, or output_rc(), if those seem appropriate).

Tim Hall's original work can be downloaded at https://oracle-base.com/dba/miscellaneous/cvs.sql or seen at https://oracle-base.com/dba/script?category=miscellaneous&file=csv.sql 

Tim's work made a compiled package called "CSV".  This is an adaptation of his work into a bunch of anonymous procedures living in the "DECLARE" section of an anonymous PL/SQL block.

It may be useful in contexts where storing a procedure into your database is not an option.
*/

DECLARE
 g_out_type    VARCHAR2(1)  := 'F';
 g_sep         VARCHAR2(5)  := ',';
 g_add_quotes  BOOLEAN      := TRUE;
 g_quote_char  VARCHAR2(1)  := '"';

 -- Alter separator from default.
 PROCEDURE set_separator (p_sep  IN  VARCHAR2) AS
 BEGIN
      g_sep := p_sep;
 END set_separator;

 -- Alter separator from default.
 PROCEDURE set_quotes (p_add_quotes  IN  BOOLEAN := TRUE,
                         p_quote_char  IN  VARCHAR2 := '"') AS
 BEGIN
  g_add_quotes := NVL(p_add_quotes, TRUE);
  g_quote_char := NVL(SUBSTR(p_quote_char,1,1), '"');
 END set_quotes;

 -- Handle put to file or screen.
 PROCEDURE put (p_file  IN  UTL_FILE.file_type,
      p_text  IN  VARCHAR2) AS
 BEGIN
  IF g_out_type = 'F' THEN
   UTL_FILE.put(p_file, p_text);
  ELSE
   DBMS_OUTPUT.put(p_text);
  END IF;
 END put;

 -- Handle newline to file or screen.
 PROCEDURE new_line (p_file  IN  UTL_FILE.file_type) AS
 BEGIN
  IF g_out_type = 'F' THEN
   UTL_FILE.new_line(p_file);
  ELSE
   DBMS_OUTPUT.new_line;
  END IF;
 END new_line;

 -- Do the actual work.
 PROCEDURE generate_all (p_dir        IN  VARCHAR2,
                         p_file       IN  VARCHAR2,
                         p_query      IN  VARCHAR2,
                         p_refcursor  IN OUT  SYS_REFCURSOR) AS
  l_cursor    PLS_INTEGER;
  l_rows      PLS_INTEGER;
  l_col_cnt   PLS_INTEGER;
  l_desc_tab  DBMS_SQL.desc_tab2;
  l_buffer    VARCHAR2(32767);
  l_is_str    BOOLEAN;
  l_file      UTL_FILE.file_type;
 BEGIN
  IF p_query IS NOT NULL THEN
   l_cursor := DBMS_SQL.open_cursor;
   DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);
  ELSIF p_refcursor%ISOPEN THEN
   l_cursor := DBMS_SQL.to_cursor_number(p_refcursor);
  ELSE
   RAISE_APPLICATION_ERROR(-20000, 'You must specify a query or a REF CURSOR.');
  END IF;
  
  DBMS_SQL.describe_columns2 (l_cursor, l_col_cnt, l_desc_tab);
  
  FOR i IN 1 .. l_col_cnt LOOP
   DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );
  END LOOP;
  
  IF p_query IS NOT NULL THEN
   l_rows := DBMS_SQL.execute(l_cursor);
  END IF;
        
  IF g_out_type = 'F' THEN
   l_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);
  END IF;
  
  -- Output the column names.
  FOR i IN 1 .. l_col_cnt LOOP
   IF i > 1 THEN
    put(l_file, g_sep);
   END IF;
   put(l_file, l_desc_tab(i).col_name);
  END LOOP;
  new_line(l_file);

  -- Output the data.
  LOOP
   EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;
   
   FOR i IN 1 .. l_col_cnt LOOP
    IF i > 1 THEN
     put(l_file, g_sep);
    END IF;
    
    -- Check if this is a string column.
    l_is_str := FALSE;
    IF l_desc_tab(i).col_type IN (DBMS_TYPES.typecode_varchar,
                                  DBMS_TYPES.typecode_varchar2,
                                  DBMS_TYPES.typecode_char,
                                  DBMS_TYPES.typecode_clob,
                                  DBMS_TYPES.typecode_nvarchar2,
                                  DBMS_TYPES.typecode_nchar,
                                  DBMS_TYPES.typecode_nclob) THEN
     l_is_str := TRUE;
    END IF;
    
    DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);
    -- Optionally add quotes for strings.
    IF g_add_quotes AND l_is_str  THEN
     put(l_file, g_quote_char); 
     put(l_file, l_buffer);
     put(l_file, g_quote_char);
    ELSE
     put(l_file, l_buffer);
    END IF;
   END LOOP;
   new_line(l_file);
  END LOOP;
  -- End of "output the data" section.
  
  IF UTL_FILE.is_open(l_file) THEN
   UTL_FILE.fclose(l_file);
  END IF;
  
  DBMS_SQL.close_cursor(l_cursor);
  
  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_cursor) THEN
     DBMS_SQL.close_cursor(l_cursor);
    END IF;
    DBMS_OUTPUT.put_line('ERROR: ' || DBMS_UTILITY.format_error_backtrace);
   RAISE;
 END generate_all;

 -- Stub to generate a CSV from a query.
 PROCEDURE generate (p_dir        IN  VARCHAR2,
                     p_file       IN  VARCHAR2,
                     p_query      IN  VARCHAR2) IS
  l_cursor  SYS_REFCURSOR;
 BEGIN
     g_out_type := 'F';
     generate_all (p_dir        => p_dir,
                   p_file       => p_file,
                   p_query      => p_query,
                   p_refcursor  => l_cursor);
 END generate;

 -- Stub to generate a CVS from a REF CURSOR.
 PROCEDURE generate_rc (p_dir        IN  VARCHAR2,
                        p_file       IN  VARCHAR2,
                        p_refcursor  IN OUT SYS_REFCURSOR) AS
 BEGIN
  g_out_type := 'F';
  
  generate_all (p_dir        => p_dir,
                p_file       => p_file,
                p_query      => NULL,
                p_refcursor  => p_refcursor);
 END generate_rc;

 -- Stub to output a CSV from a query.
 PROCEDURE output (p_query  IN  VARCHAR2) AS
  l_cursor  SYS_REFCURSOR;
 BEGIN
  g_out_type := 'D';
  generate_all (p_dir        => NULL,
                p_file       => NULL,
                p_query      => p_query,
                p_refcursor  => l_cursor);
 END output;

 -- Stub to output a CSV from a REF CURSOR.
 PROCEDURE output_rc (p_refcursor  IN OUT SYS_REFCURSOR) AS
 BEGIN
  g_out_type := 'D';
  generate_all (p_dir        => NULL,
                p_file       => NULL,
                p_query      => NULL,
                p_refcursor  => p_refcursor);
 END output_rc;



BEGIN
 -- --------------------------------------
 --                MAIN
 -- --------------------------------------
 
 -- Run the code to sysout
 output(p_query => 'SELECT * FROM SAMPLE_TABLE FETCH FIRST 5 ROWS ONLY');
 
 -- Run the code to a file
 generate('MY_ORACLE_DIR_HANDLE', 'my_filename.csv', p_query => 'SELECT * FROM SAMPLE_TABLE FETCH FIRST 5 ROWS ONLY');
 
END;
/