Lot's of DBAs may know the ociuldr utility, to extract rows from Oracle to text file. Now I rewrite ociuldr with OCI 8, and rename it to SQLULDR2 with better performance (almost double) and some good new features, and still free of charge.
Write the connect and SQL information in a parameter file to improve the security, and tell you the data volume information in the log output, and the batch option now specify a size for each text file if you don't want to generate one big text file. Such as follwoing.
D:\OracleClient>sqluldr2 parfile=testpar.txt batch=100 file=uldrdata_%b.txt
0 rows exported at 2009-05-05 17:54:59, size 0 MB.
output file uldrdata_1.txt closed at 327329 rows, size 104 MB.
output file uldrdata_2.txt closed at 283306 rows, size 204 MB.
128024 rows exported at 2009-05-05 17:55:18, size 248 MB.
output file uldrdata_3.txt closed at 128024 rows, size 250 MB.
Another important thing is that SQLULDR2 can be compile into 64 bit binary file, which ociuldr cannot. so you don't need to create a shell script to wrap the SQLULDR2 utility to use the 32 bit Oracle client library. Somebody told me that they cannot compile ociuldr into 64bit program before, now it's resolved.
However if you feel SQLULDR2 really give you great help, you can make a donation with paypal.
Comments (16)
Hi
Do you have a solaris version of SQLULDR2 available?
Posted by Hans | May 7, 2009 1:02 AM
I don't have Solaris (Sparc) machine to compile it. Sun's CPU is slow to do text unload due to low CPU frequency.
I will try to provide the binary for Solaris anyway, just wait few days.
Posted by anysql | May 7, 2009 8:19 AM
I have a few questions regarding sqluldr2:
1) Is the source available now for sqluldr2? Do you have compile instructions?
2) If I remember correctly, I had to recompile ociuldr to get the datetime format that I wanted? Does the same apply here?
2) Is sqluldr2 "option compatible" with ociuldr?
3)Using sqluldr2, I get the following error?
dba@scqc02:~/sustain/bin> sqluldr2_linux_10204.bin
sqluldr2_linux_10204.bin: error while loading shared libraries: libclntsh.so: wrong ELF class: ELFCLASS64
I have 64-bit Oracle client installed. Is the available file 64-bit? How can I get a 64-bit binary?
Cheers,
Keehan
Posted by Keehan | May 8, 2009 4:04 AM
Answer:
1, Source not avaiable now.
2, The datatime format is always "yyyy-mm-dd hh24:mi:ss".
3, Fully compatible, except the batch option.
4, The linux binary I provided here is 32bit, you can install a 32bit Oracle instant client to make it work first.
Thanks.
Posted by anysql | May 8, 2009 7:06 AM
I've really been working hard to get all the 64-bit odbc stuff to work together (unixodbc, oracle, FreeTDS, etc). I'd really rather not throw a 32-bit wrench into the box. Is there any other way?
I was reading a bit more and saw that ociuldr now compiles 64-bit? Would that work with the 64-bit client? Do you have the compile command that would build that?
Thanks again,
Keehan
Posted by Keehan | May 8, 2009 7:50 AM
Tell me your 64 bit hardware details, IA64? Or x86_64?
For Oracle, there is always 32 bit client library installed at server side, not all the Oracle utilities are now 64 bit programs.
Thanks.
Posted by anysql | May 8, 2009 9:57 AM
I am using x86_64 on SUSE Linux. I have only installed the 64-bit Oracle client. I do not have a local Oracle db server. I currently use ociuldr strictly to pull from remote databases.
Thanks,
Keehan
Posted by Keehan | May 9, 2009 1:46 AM
Ok, I will provide x86_64 Linux binary soon, but the SQLULDR2 32 bit linux binary can run on x86_64.
Posted by anysql | May 9, 2009 6:35 AM
I get the same error as 3) above:
dba@scqc02:~/sustain/bin> ./sqluldr2_linux_10204.bin
./sqluldr2_linux_10204.bin: error while loading shared libraries: libclntsh.so: wrong ELF class: ELFCLASS64
It would probably work on x86_64 if I had the 32-bit Oracle client. I am guessing that it is failing because all I have is the 64-bit Oracle client.
Thanks,
Keehan
Posted by Keehan | May 9, 2009 9:24 AM
Please set LD_LIBRARY_PATH to $ORACLE_HOME/lib32, and then execute it.
export LD_LIBRARY_PATH=$ORACLE_HOME/lib32
./sqluldr2_linux_10204.bin
Thanks.
Posted by anysql | May 9, 2009 9:39 AM
That changed things, but it still looks like I have an issue.
dba@scqc02:~/sustain/bin> ./sqluldr2_linux_10204.bin
./sqluldr2_linux_10204.bin: error while loading shared libraries: /opt/oracle/product/10.2/client_1/lib32/libclntsh.so: file too short
dba@scqc02:~/sustain/bin> echo $LD_LIBRARY_PATH
/usr/odbc/lib:/usr/odbc/drivers:/opt/teradata/tdicu/lib64:/opt/teradata/teragss/suselinux-x8664/client/lib:/opt/oracle/product/10.2/client_1/lib:/opt/oracle/product/10.2/client_1/lib32
What do you think? Sorry for the hassle.
Keehan
Posted by Keehan | May 9, 2009 1:00 PM
The reason is that I compile linux binary under RedHat Linux, not on SuSE Linux, there are some compatiable issues.
Posted by anysql | May 9, 2009 4:51 PM
Isn't /opt/oracle/product/10.2/client_1/lib32/libclntsh.so provided as part of the Oracle client though?
Posted by Keehan | May 12, 2009 10:20 AM
If there is lib32 directory, then put it at the begining of LD_LIBRARY_PATH.
if [ -d ${ORACLE_HOME}/lib32 ]; then
LD_LIBRARY_PATH=${ORACLE_HOME}/lib32:${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
else
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${ORACLE_HOME}/lib64:${LD_LIBRARY_PATH}
fi
I use the above shell to set the right library path.
Posted by anysql | May 12, 2009 11:58 AM
I don't think LD_LIBRARY_PATH is the issue. Looking back at my post from May 9, 2009 at 1:00 PM, the error states "/opt/oracle/product/10.2/client_1/lib32/libclntsh.so: file too short". So, the tool is looking in the correct location, but the file found in lib32 is too short. In fact, the file it is finding is a zero byte file. The only non-zero byte libclntsh.so I have is in lib:
dba@scqc02:/opt/oracle/product/10.2/client_1/lib> ls -la libclntsh*
lrwxrwxrwx 1 oracle oinstall 17 2009-05-11 13:35 libclntsh.so -> libclntsh.so.10.1
-rwxr-xr-x 1 oracle oinstall 20691699 2009-05-11 13:35 libclntsh.so.10.1
Is there anything else I can do?
Posted by Keehan | May 15, 2009 6:34 AM
Can you install the Oracle Instant Client 10.2.0.4 version?
Posted by anysql | May 15, 2009 7:25 AM