CODEWITHSUNDEEP
oracle-database
nath
nath

Reputation: 2848

Load data to remote DB using sqlldr

I wanted to load data to remote db using sqlldr.I did it using following command

>sqlldr GANUKA/GANUKA@jdbc:oracle:thin:@172.21.0.180:1521:orcl control=D:\Work\CLSTMAS.ctl 
log=D:\Work\CLSTMAS.log

But it gives the following error.

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

Need a help

Upvotes: 15

Views: 50034

Answers (3)

El Fadel Anas
El Fadel Anas

Reputation: 1721

I had to edit the tsnames.ora file on $ORACLE_HOME/network/admin directory. I added my database config with an alias

mmdatabase =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.0.45)(PORT = 1521))
    (CONNECT_DATA =
      (SID = mySID)
    )
  )

now i can run my sqlldr command like that

sqlldr mylog/mypass@mmdatabase control=ctrlfile.ctl data=datafile.csv bad=badfile.BAD log=logfile.LOG

Upvotes: 0

gaoagong
gaoagong

Reputation: 1255

I ended up having to use a thin client connection string. I couldn't get @Codo 's solution to work.

sqlldr \'username/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.com)(PORT=1111)))(CONNECT_DATA=(SID=MYSIDE)(SERVER=DEDICATED)))\' control=loader.ctl data=data.csv

Upvotes: 3

Codo
Codo

Reputation: 78795

You're mixing up two different worlds here. One is the OCI world where sqlldr lives. It expects Oracle instance names defined in TNSNAMES.ORA (or a similar service). The other world is the JDBC world that uses connection identifiers with words like "jdbc" or "thin".

So you have two options:

  • If your environment has a proper TNS setup, you have to change the command line to something like sqlldr GANUKA/[email protected] control=...

  • If not, you can use an Easy Connect string: sqlldr GANUKA/GANUKA@//172.21.0.180:1521/orcl control=...

Upvotes: 36

Related Questions