SQL Plus works, but sqlldr does not

Posted on 2005-04-12
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

    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 47

    Expert Comment

    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.
    LVL 8

    Expert Comment

    by:Pierrick LOUBIER
    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.

    Author Comment

    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.
    LVL 7

    Expert Comment

    by:Mehul Shah
    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

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

    Author Comment

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

    LVL 1

    Accepted Solution

    Submitted to PAQ with points refunded (125)

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Oracle Cursor 5 50
    PL/SQL Proving a negative 3 33
    Number Format 1 30
    Getting network error using utl tcp package 7 58
    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now