This article presents how-to dump SQL as CSV files using PL/SQL

Prepare data to dump

CREATE TABLE test_tbl
(
  id1 NUMBER,
  id2 DATE,
  id3 VARCHAR2(10),
  id4 CLOB
);

BEGIN
  FOR i IN 1..100
  LOOP
    INSERT INTO test_tbl VALUES(i, SYSDATE+1, i,i);
  END LOOP;
  
  COMMIT;
END;
/

SELECT * FROM test_tbl
ORDER BY 1;

ID1                   ID2  ID3  ID4
---  --------------------  ---  ---
  1   17.09.2016 09:59:49    1    1
  2   17.09.2016 09:59:49    2    2
  3   17.09.2016 09:59:49    3    3
...
 98   17.09.2016 09:59:49   98   98
 99   17.09.2016 09:59:49   99   99
100   17.09.2016 09:59:49  100  100

PLSQL method

It’s very popular and common requirements to dump SQL as CSV. I have created simple function with following parameters:

• in_sql – sql to dump as csv
• in_line_sep – extra line separator added in end of line
• in_col_sep – separator of columns
• in_left_brk – left bracket for a column
• in_right_brk – right bracket for a column
• in_header – header for the file
• in_header_type – OWN writes in first line in_header, COLUMNS generates header based on parsed columns for in_sql for null it generates nothing
• in_dir – oracle directory to dump file
• in_filename – file name to dump
• in_date_format – format of date fro dumped columns of type DATE

create or replace FUNCTION fn_dump_csv
(
  in_sql           IN CLOB,
  in_line_sep      IN VARCHAR2 DEFAULT '',
  in_col_sep       IN VARCHAR2 DEFAULT ',',
  in_left_brk      IN VARCHAR2 DEFAULT '''',
  in_right_brk     IN VARCHAR2 DEFAULT '''',
  in_header        IN CLOB     DEFAULT NULL,
  in_header_type   IN VARCHAR2 DEFAULT 'OWN', --COLUMNS
  in_dir           IN VARCHAR2,
  in_filename      IN VARCHAR2 DEFAULT 'file.txt',
  in_date_format   IN VARCHAR2 DEFAULT 'DD.MM.YYYY HH24:MI:SS'
)
RETURN NUMBER
is
  cons_clob_code        NUMBER := 112;
  cons_varchar2_code    NUMBER := 1;
  cons_char_code        NUMBER := 96;
  cons_date_code        NUMBER := 12;
  l_output              utl_file.file_type;
  l_cur                 INTEGER;
  l_varchar2_col        VARCHAR2(32767);
  l_date_col            DATE;
  l_clob_col            CLOB;
  l_clob_all            CLOB;
  l_status              INTEGER;
  l_separator           VARCHAR2(10) DEFAULT '';
  l_cnt                 NUMBER DEFAULT 0;
  l_col_cnt             NUMBER;
  l_rec_tab             dbms_sql.desc_tab;

  PROCEDURE print_rec(rec IN dbms_sql.desc_rec) IS
  BEGIN
    l_clob_all := l_clob_all || to_clob( CHR(10) ||
    'col_type            = ' || rec.col_type        || CHR(10) ||        
    'col_maxlen          = ' || rec.col_max_len     || CHR(10) ||        
    'col_name            = ' || rec.col_name        || CHR(10) ||        
    'col_name_len        = ' || rec.col_name_len    || CHR(10) ||        
    'col_schema_name     = ' || rec.col_schema_name || CHR(10) ||        
    'col_schema_name_len = ' || rec.col_schema_name_len || CHR(10) ||
    'col_precision       = ' || rec.col_precision       || CHR(10) ||
    'col_scale           = ' || rec.col_scale           || CHR(10) ||        
    'col_null_ok         = ');

    IF (rec.col_null_ok) THEN
      l_clob_all := l_clob_all || ' true' || CHR(10);
    ELSE
      l_clob_all := l_clob_all || ' false' || CHR(10);        
    END IF;
  END;
BEGIN
  dbms_output.put_line
  (
    'Calling fn_dump_csv with parameters' || CHR(10) ||
    '  in_sql:         ' || in_sql        || CHR(10) ||   
    '  in_line_sep:    ' || in_line_sep   || CHR(10) ||
    '  in_col_sep:     ' || in_col_sep    || CHR(10) ||      
    '  in_left_brk:    ' || in_left_brk   || CHR(10) ||      
    '  in_right_brk:   ' || in_right_brk  || CHR(10) ||      
    '  in_dir:         ' || in_dir        || CHR(10) ||      
    '  in_filename:    ' || in_filename   || CHR(10) ||      
    '  in_date_format: ' || in_date_format
  );

  l_output := utl_file.fopen( in_dir, in_filename, 'w', 32767 );

  ---------------------------------------
  -- Parse and describe
  ---------------------------------------
  l_cur := dbms_sql.open_cursor;

  dbms_sql.parse(  l_cur,  in_sql, dbms_sql.NATIVE );

  ---------------------------------------
  --describe columns
  ---------------------------------------
  dbms_sql.describe_columns(l_cur, l_col_cnt, l_rec_tab);

  FOR I IN 1..l_rec_tab.COUNT
  LOOP
    print_rec(l_rec_tab(i));
  END LOOP;
  
  dbms_output.put_line(l_clob_all);
  ---------------------------------------
  -- Define columns
  ---------------------------------------
  FOR i IN 1..l_col_cnt
  LOOP
    IF l_rec_tab(i).col_type IN ( cons_varchar2_code, 
                                  cons_char_code ) THEN
      dbms_sql.define_column
      ( 
        l_cur, I, l_varchar2_col, 
        l_rec_tab(i).col_max_len
      );
    ELSIF l_rec_tab(i).col_type = cons_clob_code THEN
      dbms_sql.define_column(l_cur, i, l_clob_col);
    ELSIF l_rec_tab(i).col_type = cons_date_code THEN
      dbms_sql.define_column(l_cur, i, l_date_col);
    ELSE
      dbms_sql.define_column
      (
        l_cur, I, l_varchar2_col, 
        l_rec_tab(i).col_max_len
      );
    END IF;
  END LOOP;    

  l_status := dbms_sql.EXECUTE(l_cur);

  -------------------------
  --write header
  -------------------------
  IF in_header_type = 'OWN'  AND in_header IS NOT NULL THEN    
    utl_file.put( l_output, in_header );
    utl_file.put( l_output, in_line_sep );
    utl_file.new_line( l_output );
  ELSIF  in_header_type = 'COLUMNS' THEN    
    l_separator := in_col_sep;
    
    FOR i IN 1..l_col_cnt
    LOOP      
      IF I = 1 THEN
        utl_file.put( l_output, in_left_brk );
        utl_file.put( l_output, l_rec_tab(i).col_name );
        utl_file.put( l_output, in_right_brk );
      ELSE
        utl_file.put( l_output, l_separator );
        utl_file.put( l_output, in_left_brk );
        utl_file.put( l_output, l_rec_tab(i).col_name );
        utl_file.put( l_output, in_right_brk );
      END IF;        
    END LOOP;      

    utl_file.put( l_output, in_line_sep );
    utl_file.new_line( l_output );
  end if;

  -------------------------
  --write rest of the file
  -------------------------
  LOOP    
    EXIT WHEN ( dbms_sql.fetch_rows(l_cur) <= 0 );      
    l_separator := '';

    FOR I IN 1 .. l_rec_tab.COUNT      
    LOOP      
      IF l_separator IS NOT NULL THEN
        utl_file.put( l_output, l_separator );
      END IF;
      
      IF in_left_brk IS NOT NULL THEN
        utl_file.put( l_output, in_left_brk );
      END IF;

      IF l_rec_tab(I).col_type IN ( cons_varchar2_code, 
                                    cons_char_code) THEN
        dbms_sql.COLUMN_VALUE( l_cur, i, l_varchar2_col );
        utl_file.put( l_output, l_varchar2_col );
      ELSIF l_rec_tab(I).col_type = cons_clob_code THEN
        dbms_sql.COLUMN_VALUE(l_cur, i, l_clob_col);
        utl_file.put( l_output, l_clob_col );
      ELSIF l_rec_tab(I).col_type = cons_date_code THEN
        dbms_sql.COLUMN_VALUE(l_cur, I, l_date_col);
        utl_file.put( l_output, to_char(l_date_col, in_date_format) );
      ELSE        
        dbms_sql.COLUMN_VALUE(l_cur, i, l_varchar2_col);
        utl_file.put( l_output, l_varchar2_col );
      END IF;

      IF in_right_brk IS NOT NULL THEN
        utl_file.put( l_output, in_right_brk );
      END IF;

      l_separator := in_col_sep;
    END LOOP;      

    IF in_line_sep IS NOT NULL THEN
      utl_file.put( l_output,in_line_sep);
    END IF;

    utl_file.new_line( l_output );
    l_cnt := l_cnt+1;
  END LOOP;

  dbms_sql.close_cursor(l_cur);

  utl_file.fclose( l_output );

  dbms_output.put_line( 'Dump completed rows: '||l_cnt );

  RETURN l_cnt;
EXCEPTION
  WHEN OTHERS THEN
    IF utl_file.is_open(l_output) THEN      
      utl_file.fclose( l_output );
    END IF;
    
    IF dbms_sql.is_open(l_cur) THEN
      dbms_sql.close_cursor(l_cur);
    END IF;
    
    dbms_output.put_line( dbms_utility.format_error_stack );
    dbms_output.put_line( dbms_utility.format_call_stack );
    dbms_output.put_line( dbms_utility.format_error_backtrace );
    
    RETURN 0;
END fn_dump_csv;

Examples

First directory must be created to dump files

CREATE DIRECTORY test_dir AS '/tmp';

1. Generate simple file with all default settings

set serveroutput on
DECLARE
  l_ret number;
BEGIN
  l_ret := fn_dump_csv
           ( 
             in_sql => 'select * from test_tbl', 
             in_dir => 'TEST_DIR' 
           );
  DBMS_OUTPUT.PUT_LINE('Return code = ' || l_ret);
END;
/

Calling fn_dump_csv with parameters
  in_sql:         select * from test_tbl
  in_line_sep:    
  in_col_sep:     ,
  in_left_brk:    '
  in_right_brk:   '
  in_dir:         TEST_DIR
  in_filename:    file.txt
  in_date_format: DD.MM.YYYY HH24:MI:SS

col_type            = 2
col_maxlen          = 22
col_name            = ID1
col_name_len        = 3
col_schema_name     = 
col_schema_name_len = 0
col_precision       = 0
col_scale           = -127
col_null_ok         =  true

...

Dump completed rows: 100
Return code = 100

check file in UNIX

[oracle@oel6 ~]$ cat /tmp/file.txt
'1','28.09.2016 13:35:01','1','1'
'2','28.09.2016 13:35:01','2','2'
...
'99','28.09.2016 13:35:01','99','99'
'100','28.09.2016 13:35:01','100','100'

2. Dump file with own header. It’s the same except adding extra parameter.

set serveroutput on
DECLARE
  l_ret number;
BEGIN
  l_ret := fn_dump_csv
           ( 
             in_sql => 'select * from test_tbl', 
             in_dir => 'TEST_DIR',
             in_header => 'simple header',
             in_header_type => 'OWN' 
           );
  DBMS_OUTPUT.PUT_LINE('Return code = ' || l_ret);
END;
/

Calling fn_dump_csv with parameters
  in_sql:         select * from test_tbl
  in_line_sep:    
  in_col_sep:     ,
  in_left_brk:    '
  in_right_brk:   '
  in_dir:         TEST_DIR
  in_filename:    file.txt
  in_date_format: DD.MM.YYYY HH24:MI:SS

...

Dump completed rows: 100
Return code = 100

check file in UNIX

[oracle@oel6 ~]$ cat /tmp/file.txt
simple header
'1','28.09.2016 13:35:01','1','1'
'2','28.09.2016 13:35:01','2','2'
...
'99','28.09.2016 13:35:01','99','99'
'100','28.09.2016 13:35:01','100','100'

3. Dump file with header generated from columns, add extra brackets for columns left ( right ), change column separator to |, change date format as ‘YYYY DD MM’, add extra characters in end of line ‘$$$’

set serveroutput on
DECLARE
  l_ret number;
BEGIN
  l_ret := fn_dump_csv
           ( 
             in_sql => 'select * from test_tbl', 
             in_dir => 'TEST_DIR',
             in_header_type => 'COLUMNS',
             in_col_sep => '|',
             in_left_brk => '(',
             in_right_brk => ')',
             in_line_sep => '$$$',
             in_date_format => 'YYYY DD MM' 
           );

  DBMS_OUTPUT.PUT_LINE('Return code = ' || l_ret);
END;
/

Calling fn_dump_csv with parameters
  in_sql:         select * from test_tbl
  in_line_sep:    $$$
  in_col_sep:     |
  in_left_brk:    (
  in_right_brk:   )
  in_dir:         TEST_DIR
  in_filename:    file.txt
  in_date_format: YYYY DD MM

...

Dump completed rows: 100
Return code = 100

check file in UNIX

[oracle@oel6 ~]$ cat /tmp/file.txt
(ID1)|(ID2)|(ID3)|(ID4)$$$
(1)|(2016 28 09)|(1)|(1)$$$
(2)|(2016 28 09)|(2)|(2)$$$
...
(99)|(2016 28 09)|(99)|(99)$$$
(100)|(2016 28 09)|(100)|(100)$$$

Have a fun 🙂

Tomasz