DBA Tools

Hi

Do you have a solaris version of SQLULDR2 available?

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.

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

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.

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

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.

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

Ok, I will provide x86_64 Linux binary soon, but the SQLULDR2 32 bit linux binary can run on x86_64.

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

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.

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

The reason is that I compile linux binary under RedHat Linux, not on SuSE Linux, there are some compatiable issues.

Isn't /opt/oracle/product/10.2/client_1/lib32/libclntsh.so provided as part of the Oracle client though?

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.

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?

Can you install the Oracle Instant Client 10.2.0.4 version?

Do you have a AIX version of SQLULDR2 available?

zhcx_bqs1-aibqsimp%sqluldr2_linux32_10204.bin
sqluldr2_linux32_10204.bin: syntax error at line 1: `(' unexpected

Is there anything else I can do?

you did a good job with this post. I do not necessarily agree with everything stated here, but i do appreciate the actual fact that its written in a straightforward manner and you don't sugar coat anything. Perhaps others disagree with me, however I'm simply expressing my view. Thanks for making a platform to allow any of your readers to be able to express themselves freely.

I hadn't check back in a while. The new 64-bit executable seems to work fine on SLES out of the box.

Cheers,

Keehan

This utility is great. Could you please post solaris version?

Thanks,

Hill

Sorry, I don't have solaris environment now, I will upload the solaris binary if possible.

Hi Fangxin, I can compile a Solaris version for you if you want. Send me the source file and I will send it back to you. I distribute it of course, I just want to use the tool on my Solaris machine. Let me know and thanks for all these great utilities.

Thank you for the response. It would be of great help if you could provide that. I am not sure if you are planning to release the source code. If so I could compile myself too.

Another question - Sometimes the data in some of the columns would contain the delimiter value that we provide. Is there a way to get around that. It would be helpful to provide an option to replace it with some other character.

You can use the quote option to double quote it, such as:

sqluldr2 ... quote=0x22 ...

All non digital and non date columns will be quoted.

Hi,

I downloaded your executable for sqludr2 for AIX-64 bit and it worekd perfectly. I was wondering if the source code for this program is available? We would like to have the source code in order to compile it for different versions of AIX and Oracle. I would appreciate it if you can let me know.

Thanks,
Denise

I haven't been ready for the source open.

The binary provided can run well on Oracle 9i, 10g, and 11g for AIX, you don't need to recompile it.

Thank you for your prompt response.
There is a slight problem with using your executable. We would like to create a wrapper for your program to avoid a massive change in our product! We would like to replace an existing unload utility which used different input arguments. For this we preferably need the source code. Do you know when you will be ready to release the source code? If you are willing to sell your product, I can even put you in contact with our sales department.

Thank you,
Denise

You can tell me the arguments you used in the old unload utility, maybe I can change it for you.

Hi,

The name of the utility is 'oraunload'. It gets the username and the password from environment variables ORAUSER and ORAPASSWD respectively and it unloads the data delimited with "|" in a file by the name of .dat. Here are the two ways that we call this utility.

oraunload SELECT SAH_SSN_BRH,
SAH_PRG,
SAH_SIZE,
'',
SAH_WDTH,
SAH_LGTH,
'hello',
SAH_REF_PFX,
SAH_REF_NO,
SAH_REF_ITM,
from ssssss_rec
!

The data will be unloaded to ssssss.dat

oraunload -i "select * from blnbnm_rec"

The data will be unloaded to blnbnm.dat

Thank you,
Denise

Is there an email address which I can use for correspondance or you prefer the posts?

HI, Denise, you can contact me with anysql@live.com, both MSN and email.

Hi,

I sent you an email about two weeks ago and did not receive any response. Can you please verify that you have recieved it?

Thanks,
Denis

Hi Fangxin,

First of all, thanks for sharing this very useful tool. I used the sqluldr2 to unload a large table with about 62million rows. However, the unload was still going after exporting 1058000000 rows with a file larger than 300G. Any idea why????

Looking forward to your response!

Hi, William:

Can you try unload other big tables, not sure how it happen. Does it happen again if you reexecute the same job. What platform are your running?