- 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:
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!
Trick with ORA error and the number of rows in the same time is fine.
ReplyDeleteHi Damir,
ReplyDeleteOracle "dbms_sql" is deep hole of treasure. Thank you for pointing us some of it's content.
Janice
I thank Jack to pointing out some small problems with mixed names(tools package and standalone function), so I recheck code and correct problematic parts.
ReplyDeleteDamir
to export raw content you could use following code in your sql_to_csv stored function:
ReplyDeletein declare section add:
l_column_value_raw RAW(2000);
then in body of function make following modification:
if l_table_desc(i).col_type in (8, 24, 112, 113, 114, 115) then
l_column_value:= '***UNPRINTABLE***';
elsif l_table_desc(i).col_type in (23) then -- RAW, see cdcore.sql for mapping of col_type to datatype
dbms_sql.column_value (cur_da_cursor,i,l_column_value_raw);
l_column_value:=rawtohex(l_column_value_raw);
else
dbms_sql.column_value (cur_da_cursor,i,l_column_value);
end if;
.
this prints hexadecimal digits in the output file that later on (when the csv file is used as external table) can be converted back to raw using hextoraw()
@Andre
ReplyDeleteNice hint. Thanks!