When export oracle data to different database platform, text file is always a good choice and may be the only choice, if they cannot connect directly. And some time I was required to provide some rows in excel CSV file (a "," separated text file) or excel xls file (a tab separated text file) to business people. And also provide large volume data for data warehouse team to load them into Teradata system.
So we need a good tool to unload rows to these files from oracle database, I wrote one few years ago. It's ociuldr (Binary/Source), OCI is the short name of Oracle Call Interface means high performance, uldr is the short name of unloader. I already wrote a very detailed document for this utility. I will introduce how to generate these kinds of text file here briefly.
We want to list the table name of schema test into CSV file.
ociuldr user=test/test query="select * from tab" field=0x2c file=test_tables.csv
We want to do the same job, but save it as a excel xls file.
ociuldr user=test/test query="select * from tab" field=0x09 file=test_tables.xls
Now we need to unload lot's of data (from table SALES, and the query is really complex, so I save it as sales.sql) to data warehouse team, the asked to seperate the column value with line char ("|").
ociuldr user=sales/sales sql=sales.sql field=0x7c file=sales_for_dw.dat
The tools provide a lot of option for performance control, and can generate a sqlldr control file for you to easily load the unloaded data to another oracle database.
Comments (19)
A detailed PDF document about this utility has be uploaded.
Posted by anysql | September 20, 2007 11:26 PM
Hi, your tool is really wonderful and very useful.
I have some errors when i perfom your tool. Specific when i put all columns "select *" and some of the colums are CLOB type.
For example:
0 rows exported at 2008-06-16 20:04:14
ORA-00251: LOG_ARCHIVE_DUPLEX_DEST cannot be the same destination as string
5 rows exported at 2008-06-16 20:04:14
output file prueba.csv closed at 5 rows.
and
0 rows exported at 2008-06-16 20:21:32
ORA-01405: fetched column value is NULL
7 rows exported at 2008-06-16 20:21:32
output file prueba.csv closed at 7 rows.
Can you tell me how to solve this problem.
Please, Regards.
Posted by Antonio Zamora | June 17, 2008 10:30 AM
I wrote the ociuldr with OCI 7 interface, it does not support CLOB/BLOB data type.
I will take time to rewrite it with OCI 8 interface, it will support CLOB/BLOB data types, and will be ociuldr2.
Posted by anysql | June 18, 2008 9:14 AM
It is a great tool.
Is it possible to define the array fetch size?
Posted by Tom | June 20, 2008 9:25 PM
You can use the array option to change the array size, default value is 50, which is enough normally.
ociuldr array=... ......
Posted by anysql | June 20, 2008 11:25 PM
Hai,
i need to unload oracle table data to csv file,
my table includes blob/clob data.
how to achieve ,
thanks,
saven
Posted by juniorDev | October 22, 2008 8:41 PM
Hi Lou Fangxin,
You have done a marvelous job in writing the code for ociuldr.
I got exactly what I wanted. The code is very compact and beautiful.
Thanks for posting. That saved me lot of time.
Regards,
Yeggi
Posted by yeggi | October 31, 2008 11:31 PM
Very nice to know that ociuldr can help you.
Posted by anysql | November 2, 2008 9:24 AM
Hi Fangxin Lou,
Thank you for developing this tool. It is very useful.
The lack of such a tool from Oracle itself is quite disappointing.
You have done a great service by providing the missing tool.
I needed to compile the program for my OS. Your instructions were quite clear and compiling it was an easy step. Using the program was also simple and straight forward.
Congratulations,
Les
Posted by Les | January 23, 2009 11:58 PM
Love the ociuldr program. I've been able to migration tons of data from 9i to 10g. Any timeframe on when ociuldr2 will be ready? I'm running into tables with CLOB data and I'm getting the LOG_ARCHIVE_DUPLEX_DEST error.
Thanks,
Mark
Posted by Mark | January 27, 2009 12:17 AM
I am not sure about the LOG_ARCHIVE_DUPLEX_DEST error. There are many ociuldr users in China also, we did not get this error.
This parameter is used to specify the second archive log location, so it should has no relation to ociuldr.
For ociuldr2, I will try to finish it in 2009, with full long & CLOB & BLOB support.
Thanks for all of your support, it gave me blave to write more.
Posted by anysql | January 27, 2009 9:15 AM
I think my error is do to the amount of data in the table, 1.4 Million records and the CLOB column mostly contains NULL except for 131,000 that does contain data. I'm able to get the 131,000 but whenI try records with NULL, I get the error again. I think I have a work around for the time being.
Thanks again,
Mark
Posted by Mark | January 28, 2009 7:11 AM
Hi Fangxin Lou,
Can the results go to stdout, instead of a file? I want to pipe the results into another program.
Thanks,
Les
Posted by Les | January 29, 2009 1:31 AM
I will modify the code to output to stdout, just wait few days, I will enable it after China spring holiday (after Feb 1). Or you can modify it by yourself.
Posted by anysql | January 30, 2009 3:12 PM
ociuldr does not support LOB type well now. Seems I should start to write the second version of ociuldr.
Posted by anysql | January 30, 2009 3:17 PM
Will the second version also support NCLOB and NVARCHARS?
Posted by Mark | February 12, 2009 4:07 AM
I will test the program with NCLOB and NVARCHARS values.
I am busy in writing documents about WebChart utility, it's another useful free tool.
Posted by anysql | February 12, 2009 8:11 AM
Thanks for the wonderful OCI code. I started testing this on my x64 RHEL4 box with Oracle database 10.2.0.2. I'm looking at unloading huge tables. By huge tables I mean 500+GB of tables with partitions. I found a couple of issues with ociuldr so would like to bring it your notice.
1. It writes everything to one single text file. I used %d and batch option but still it writes to single file. Find below the command I used:
ociuldr.bin user=secret/secret query="select * from ngtv_ysm" batch=500000 field='0x01' record='0x02' file="/u06/ociuldr/ngtv_ysm_%d.txt" log="/u06/ociuldr/ngtv_ysm.log"
And it generated 1 single file called "ngtv_ysm_08.txt" with a size of 5.3G. I'm looking for something scalable
2. When a "field" seperator is specified, it puts the delimeter also after the last column which I think should not be the case.
Appreciate your response on this.
Regards
Ritesh
Posted by Ritesh | April 9, 2009 3:31 PM
For question 1: Why not run ociuldr in parallel, each partition a file. If you want to generate multiple files, use "batch=10" and file=ngtv_ysm_%b.txt", it will write 10 * 500000 rows to each file.
For question 2: I will check the code, but delimeter after last column is acceptable.
The newer code name is SQLULDR2, it's about 100% faster than ociuldr. You can find the download link at this page (download area at right side).
Posted by anysql | April 20, 2009 2:50 PM