SQL Plus works, but sqlldr does not

Posted on 2005-04-12
Medium Priority
Last Modified: 2013-12-11
I recently got a new laptop and it is running windows xp sp2.  I installed Oracle 9i and moved everything off my old one to this one.  All settings look identical.  The only difference is the machine name.

Now I am having an issue connecting to the database through some 3rd party apps, but bigger is that I am having an issue executing SQL Loader.  I have no problem with connecting via SQL*Plus, nor some other ODBC query tools, but there is one that is having an issue.  At first I just submitted a support request to the vendor and dismissed it as thier tool does not play well with XP Sp2.  But then I tried to run the sqlldr scripts that I had written. That is when I think this became more complex.

The error I get when I run sqlldr is:

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve service name

The error I get from some other apps is simply :

TNS:could not resolve service name

My tnsnames.ora file points to everything correctly.

Does anyone have any ideas?

Question by:cusas76
LVL 11

Expert Comment

ID: 13769468
Ideally SQLLDR should use the same TNS entry (Oracle Home) that Your SQL*PLUS does. Check if the Oracle Home they r using are the same or not. See that in Registry there is only one Oracle Home is there (in case of D2K Installation a separate Oracle Home is made and you get similar problems). See what's your default oracle home. TNSPING the entry that sqlldr is using.
LVL 48

Expert Comment

ID: 13771037
See if the Listener is up.
c:>lsnrctl status

After that go to tnsname.ora, listener.ora and sqlnet.ora and look if you have changed the
old machine to the new one (in the case you copied files e.g. tnsnames.ora).

Check if the listener is acessible:

c:>tnsping <alias_here> 10

Make sure that you have entered you connection string in the right file (check for different copies of tnsname.ora).

Finally add in the Registry --> ....  ---> Oracle home
TNS_ADMIN = network/admin     directory.

Expert Comment

by:Pierrick LOUBIER
ID: 13772592
Follow schwertner's comment, and replace your machine name with 'localhost' in TNSNAMES and LISTENER.ORA. You won't have to take care of network configuration any more after renaming or changing your PC.
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.


Author Comment

ID: 13791725
Sorry everyone, I should have pulled this question right away.  My issue was that I had Oracle Warehouse Builder installed and it put its bin directory in the PATH before the orahome bin directory. That is why somethings work and some didn't.

Thanks for the suggestions all, I had already tried most of these prior to posting.

Expert Comment

by:Mehul Shah
ID: 13805346
Go to the home folder first and than run sqlldr from that folder.

It happens quite often that when we install multiples homes, by default the settings points to the last installed home. So if you try to invoke sqlldr without any path than it will go to the defualt home where the tns entry might not be configured. Either you need to have similar contents in all the tnsnames.ora file on the server or you need to go to a particular oracle home and then run sqlldr



LVL 13

Expert Comment

ID: 13866770
also can you paste the contents of sqlnet.ora file ?

Author Comment

ID: 13873550
OK, I solved this, but don't know how to close it.  The issue as I said before was the PATH environment variable.


Accepted Solution

DarthMod earned 0 total points
ID: 13996776
Submitted to PAQ with points refunded (125)

Community Support Moderator

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question