SQL*Loader-704 and ORA-12154

Question

sqlldr "xxxx/xxxx@YYYYYXXXX" control=/home/local/INTERNAL/xxxxx/Presc_SQLLDR_File/Presc_SQLLDR.ctl log=/home/local/INTERNAL/xxxxx/Presc_SQLLDR_File/Presc_log.log data=home/achand/Presc_SQLLDR_File/Presc_SQLLDR.ctl

Whenever i'm trying to execute sqlldr I'm getting below error

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified

I tried tnsping YYYYYXXXX

I got below error

TNS-03505: Failed to resolve name

tnsnames.ora contain

DDCPPSD.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXYYY)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = YYYYYXXXX)
    )
  )

Answer 1

If your tnsnames.ora alias is defind as DDCPPSD.WORLD and your sqlnet.ora specifies a default domain of anything except WORLD then you need to fully qualify the alias when you use it:

tnsping DDCPPSD.WORLD

and

sqlldr xxxx/[email protected] control=...

Otherwise the default domain will be appended to the unqualified name, giving you a fully-qualified name that does not exist in your tnsnames.ora.

Read more about this in the documentation.

Answer 2

I had the same problem. If you try to connect your server to your TNS , you will see you cannot connect.

telnet your_tns_ip your_tns_port

so you need to permition your server to TNS. That was my problem at least.

Answer 3

You need to do below changes in bat file.

username/\"p@ssword\"@connect_string

Enjoy.

Answer 4

I came across this error too

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified

When I tryed call a batch like this...

sqlldr "uuuu/pppp@SSS" 

I changed the batch file for:

set oracle_sid=SSS
sqlldr uuuu/pppp

After the change worked well.

Answer 5

This error message self-explainatory. You have not specified the connect string, and just used username and password. Therefore, Oracle tries to connect to local database. You must specify username/password@connect_string, where connect_string is a name for the corresponding entry in your client side file TNSNAMES.ORA (same connect string you use in SQL Developer).

Answer 6

It worked for me after changing my password. I was using '@' as one of the special character in my password which was causing this issue. :(