SQL based export data to file

Many times in my praxis I had situation that customer(developers) asks for table export to plain text like file. Here are tipical examples of that need:

• This is situation when Oracle exp or expdp tools are not possible to use because data should be imported to some other database type (MSSQL, dBase, MySQL, MSAccess etc.)
  
• Heterogeneous services are not available (data has to transported from one place to another).
• Data comes from several tables (not only one table)
  
• Toad or other similar tools cannot be used for export because tables size are several GB (so they cannot be run from client side) and server is not possible (Linux!).
  
• You want to have table exported to text file on disk and use it as external table in Oracle when appropriate.

To make this real I have invented my own function sql_to_csv which allows me to do all that.

Implementation

Because I use utl_file I need to define output directory. For that I use one directory which I grant to PUBLIC user (for re usability in any schema):

CREATE OR REPLACE DIRECTORY 
EXPDP_DIR AS 
'D:\share';

GRANT READ, WRITE ON DIRECTORY SYS.EXPDP_DIR TO PUBLIC;

If you all ready have other directory definition this can be avoided of course.
Remember that function can be run from anywhere but result is always in directory which must be available from instance server side.

Next is to create function sql_to_csv which will do the export:

/*---------------------------------------------------------------------
 NAME    : sql_to_csv.fnc
 PURPOSE : export data retrieved from plain sql result set
 Date    : 22.10.2007.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : more tables resultset is supported as well

 Changes (DD.MM.YYYY, Name, CR/TR#):
-----------------------------------------------------------------------*/
create or replace function sql_to_csv (p_query     in varchar2, 
                                       p_dir       in varchar2,
                                       p_filename  in varchar2,
                                       p_col_separator in varchar2 default ';',
                                       p_rec_separator  in varchar2 default chr(10)
                                      ) return PLS_INTEGER
AUTHID CURRENT_USER
IS
  line_term_lin CONSTANT VARCHAR2(2) := chr(10);
  line_term_win CONSTANT VARCHAR2(2) := chr(10)||chr(13);
  
  cur_da_cursor   PLS_INTEGER DEFAULT dbms_sql.open_cursor;
  l_column_value  VARCHAR2(4000);
  l_table_desc    dbms_sql.desc_tab;
  l_status        PLS_INTEGER;
  l_column_count  PLS_INTEGER;
  l_nls_date      VARCHAR2(32);
  l_separator     VARCHAR2(10) default '';
  l_cnt           PLS_INTEGER default 0;
  l_output        utl_file.file_type;
begin
  select value into l_nls_date
    from nls_session_parameters
   where lower(parameter) = 'nls_date_format';

  l_output := utl_file.fopen(p_dir,p_filename,'w');

  execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

  dbms_sql.parse(cur_da_cursor,p_query,dbms_sql.native);

  dbms_sql.describe_columns (cur_da_cursor,l_column_count,l_table_desc);

  for i in 1 .. l_column_count loop
    dbms_sql.define_column (cur_da_cursor,i,l_column_value,4000);
  end loop;

  l_status := dbms_sql.execute(cur_da_cursor);
 
  while (dbms_sql.fetch_rows(cur_da_cursor) > 0) loop
    l_separator := '';
    for i in 1 .. l_column_count loop
      dbms_sql.column_value (cur_da_cursor,i,l_column_value);
      if l_table_desc(i).col_type in (8, 23, 24, 112, 113, 114, 115) then
        l_column_value:= '***UNPRINTABLE***';
      end if;
      utl_file.put(l_output, l_separator || l_column_value);
      l_separator := p_col_separator;     
    end loop;
    -- for default record terminators platform line termination is enough!!
    if NOT ( (p_rec_separator = line_term_win) OR (p_rec_separator = line_term_lin)) then
      utl_file.put (l_output, p_rec_separator);
    end if;
    utl_file.new_line(l_output);   
    l_cnt := l_cnt+1;
  end loop;
  dbms_sql.close_cursor(cur_da_cursor);
  utl_file.fclose(l_output); 
  execute immediate 'alter session set nls_date_format='''||l_nls_date||'''';
  return l_cnt;
exception
  when others then
    begin
      l_cnt := sqlcode ;
      dbms_output.put_line (chr(9));
      dbms_output.put_line (p_query);
    exception
      when others then
        null;
    end;   
    execute immediate 'alter session set nls_date_format='''||l_nls_date||'''';
    return l_cnt;
end sql_to_csv;

Regardless that function name sql_to_csv suggests that output is only comma separated values (what is default), as you can see, column separator and record separator can be redefined as you wish.
Use non default record separator value (p_rec_separator) if you have multi line values in resulting columns.

Example I

Let us see export from sys.dba_source table:

SQL> desc dba_source;
 Name         Null?    Type
 ------------ -------- ------------------
 OWNER                 VARCHAR2(30)
 NAME                  VARCHAR2(30)
 TYPE                  VARCHAR2(12)
 LINE                  NUMBER
 TEXT                  VARCHAR2(4000)

SQL>

To make this example more easy, only first five rows will be exported:

SQL> declare
  2    l_rows pls_integer;
  3  begin
  4    l_rows := sql_to_csv('select * from dba_source where rownum<=5',
                            'EXPDP_DIR', 
                            'dba_source_csv.csv'
                           );
  5    dbms_output.put_line('exported rows:'||l_rows);
  6  end;
  7  /
exported rows:5

PL/SQL procedure successfully completed.

SQL>

Now let's see the result of export:

D:\share>dir
 Volume in drive D is System
 Volume Serial Number is 5045-B4D2

 Directory of D:\share

25.01.2010  09:38    <dir>          .
25.01.2010  09:38    <dir>          ..
25.01.2010  09:37               374 dba_source_csv.csv
               1 File(s)            374 bytes
               2 Dir(s)  37.724.327.936 bytes free

D:\share>

Content of "dba_source_csv.csv" is:

D:\share>more dba_source_csv.csv
SYS;RE$NV_LIST;TYPE;1;TYPE     re$nv_list
SYS;RE$NV_LIST;TYPE;2;                                                        AS OBJECT
SYS;RE$NV_LIST;TYPE;3;( actx_list sys.re$nv_array,
SYS;RE$NV_LIST;TYPE;4;  MEMBER PROCEDURE add_pair(name IN varchar2, value IN sys.anydata),
SYS;RE$NV_LIST;TYPE;5;  MEMBER PROCEDURE remove_pair(name IN varchar2),
D:\share>

Example II

If you have table that has some fields which cannot be exported (i.e. LONG, LOB etc.) these columns in output will be automatically skipped and written as "***UNPRINTABLE***" in output file. In such a way you do not have to think about columns types and gives you opportunity lately edit output file as you like. However such a approach ensure that external tables will properly function (number of fields are the same).

SQL> desc tab.t_dnevnik_troskova;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATUM                                     NOT NULL DATE
 ID                                        NOT NULL NUMBER(6)
 BR_RAC                                    NOT NULL VARCHAR2(15)
 TSR_ID_TSR                                NOT NULL VARCHAR2(5)
 PAR_SIFPP                                 NOT NULL NUMBER(8)
 TSR_TIP                                   NOT NULL VARCHAR2(2)
 IZNOS                                              NUMBER(16,2)
 NAPOMENA                                           LONG
 DAT_DOK                                            DATE
 DAT_KRE                                            DATE
 OPE_KRE                                            VARCHAR2(30)
 DAT_PRO                                            DATE
 OPE_PRO                                            VARCHAR2(30)
SQL>

Notify column "NAPOMENA" which is LONG type:

Export data through mine function:

SQL> declare
  2    l_rows pls_integer;
  3  begin
  4    l_rows := sql_to_csv('select * from tab.t_dnevnik_troskova', 'EXPDP_DIR', 't_dnevnik_csv.csv');
  5    dbms_output.put_line('exported rows:'||l_rows);
  6  end;
  7  /
exported rows:3

PL/SQL procedure successfully completed.

SQL>

Result looks like:

D:\share>type t_dnevnik_csv.csv

19-svi-2008 00:00:00;1;2356;297;1822;1;1688;***UNPRINTABLE***;;19-svi-2008 13:42:11;ZAST;19-svi-2008 16:00:30;TAB
19-svi-2008 00:00:00;3;549;298;1822;1;1568;***UNPRINTABLE***;;19-svi-2008 16:13:28;TAB;;
19-svi-2008 00:00:00;5;3548/2008;291;1822;5;522;***UNPRINTABLE***;18-svi-2008 00:00:00;19-svi-2008 16:37:16;TAB;;

D:\share>

Example III

As said at the beginning, data can be exported from more then one table. Here is code that use join query definition:

select DC.CUST_FIRST_NAME||' '||DC.CUST_LAST_NAME, 
       DO.ORDER_TIMESTAMP, DO.ORDER_TOTAL
  from DEMO_CUSTOMERS dc, DEMO_ORDERS do
 where DC.CUSTOMER_ID=DO.CUSTOMER_ID

Implementation in function looks like:

SQL> declare
  2    l_rows pls_integer;
  3    l_stmt varchar2(4000);
  4  begin
  5    l_stmt := 'select DC.CUST_FIRST_NAME ||' ||''' '''|| '|| DC.CUST_LAST_NAME CUSTOMER, '||
  6              '       DO.ORDER_TIMESTAMP, DO.ORDER_TOTAL '||
  7              '  from DEMO_CUSTOMERS dc, DEMO_ORDERS do '||
  8              ' where DC.CUSTOMER_ID=DO.CUSTOMER_ID ';
  9    l_rows := sql_to_csv (l_stmt, 'EXPDP_DIR', 't_complex_query.csv');
 10    dbms_output.put_line('exported rows:'||l_rows);
 11  end;
 12  /
exported rows:10

PL/SQL procedure successfully completed.

SQL>

And the result is:

D:\share>type t_complex_query.csv
Eugene Bradley;22-srp-2009 14:58:35;500
John Dulles;05-ruj-2009 14:58:35;1200
William Hartsfield;31-kol-2009 14:58:35;599
William Hartsfield;26-kol-2009 14:58:35;1999
Fiorello LaGuardia;06-kol-2009 14:58:35;3800
Albert Lambert;01-kol-2009 14:58:35;40
Albert Lambert;27-srp-2009 14:58:35;450
Edward Logan;21-kol-2009 14:58:35;750
Edward Logan;16-kol-2009 14:58:35;40
Edward "Butch" OHare;11-kol-2009 14:58:35;250

D:\share> 

Error control

If you put query (previously one correct query) and result is like:

...
DC.CUSTOMER_ID=DO.CUSTOMER_ID
exported rows:-29283

PL/SQL procedure successfully completed.
SQL>

Then negative number of rows "exported rows:-29283" indicate indicates ORA-29283 error. In this case "invalid file operation" what says that directory doesn't exists. In this way it is easy to debug the problem.

Cheers!