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)
    )
  )

Is DDCPPSD defined in your tnsnames.ora file, on the client machine you're running SQL\*Loader from? — Alex Poole, Jun 13 '15 at 15:27
Does `tnsping ddcppsd.world` work then? If not maybe the file is in the wrong place, or TNS_ADMIN is pointing to the wrong place. Does your sqlnet.ora have a default domain entry? — Alex Poole, Jun 13 '15 at 16:45
Alex tnsping ddcppsd.world is working and sqlnet.ora have a default domain entry. — , Jun 14 '15 at 09:48

arjun kori · Answer 1 · 2020-09-30T14:47:23.273

6

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

edited Sep 30 '20 at 14:47

answered Aug 11 '16 at 09:12

arjun kori

1,090
2
14
32

score 2 · Answer 2 · answered Aug 11 '16 at 20:05

2

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. :(

answered Aug 11 '16 at 20:05

beetri

1,039
11
24
40

score 2 · Answer 3 · answered Feb 10 '17 at 06:33

2

You need to do below changes in bat file.

username/\"p@ssword\"@connect_string

Enjoy.

answered Feb 10 '17 at 06:33

sagar ranjan Swain

21
1

2

Welcome to Stackoverflow. Please take some time to read how to write a good answer. read [this](http://meta.stackexchange.com/a/7659/338114) and [this](http://stackoverflow.com/help/how-to-answer) – Sourav Ghosh Feb 10 '17 at 06:47

score 1 · Answer 4 · answered Jun 14 '15 at 10:53

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/xxxx@DDCPPSD.WORLD 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.

score 1 · Answer 5 · answered Aug 01 '17 at 06:21

1

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.

answered Aug 01 '17 at 06:21

Tolga ÇELİK

39
5

score 0 · Answer 6 · answered Mar 19 '19 at 15:01

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.

SQL*Loader-704 and ORA-12154

6 Answers6