Solved

connection string in the Oracle sqlldr command

Posted on 2011-03-02
11
5,329 Views
Last Modified: 2013-11-11
I have Oracle 11g database and I am running sqlldr command (from another linux server) to load data to the targer Oracle database.

Now some database connection details changed and I do not have access to update tnsnames.ora file. I need to specify Oracle database details on the sqlldr commad itself.
I get error:

What should be the correct syntax:

My tnsnames.ora entry is:
MYDBCON=  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myHostname.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MyInstance)
    )
  )

$sqlldr login/pwd@'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myHostname.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = MyInstance)))' control='D:\sqlload\new\changes\loader1.ctl' log='D:\sqlload\new\changes\log1.txt'

LRM-00116: syntax error at 'ADDRESS_LIST' following '('

Open in new window

0
Comment
Question by:toooki
11 Comments
 

Author Comment

by:toooki
ID: 35023166
I tried this command from windows PC and it works:
sqlldr login/pwd@myHostname.com/MyInstance control='D:\sqlload\new\changes\loader1.ctl' log='D:\sqlload\new\changes\log1.txt'

But on the Linux server: I do these:

#ORACLE_HOME=/usr/prod/wsdcm/oraclient/linux/11g_64
#PATH=$PATH:$ORACLE_HOME/bin
#LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
#sqlldr login/pwd@myHostname.com/MyInstance control='/var/www/sqlload_control/load_changes_lib.ctl' log='/var/www/logs/load_changes_lib.log'
Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS, facility=UL[pb]#

But I get error message above.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35024925
check your file read/write perfmitions on linux
0
 

Author Comment

by:toooki
ID: 35032695
I am root and it seems I have read/write permissions on all files...
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 450 total points
ID: 35043531
2100 is an out of memory error, but I assume your Oracle_Home and the path you use for sqlldr are not fitting together. There are several links which claim to cover this particular error, for example http://dbataj.blogspot.com/2008/02/sqlldr-not-working-on-linuxunixsolaris.html .
0
 

Author Comment

by:toooki
ID: 35120075
Thank you.
There is a running php script that uses the same path variables on the same server (where I am trying to run the sqlldr command):
ORACLE_HOME=/usr/prod/wsdcm/oraclient/linux/11g_64
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
And then connects to a remote Oracle 11g database and run SQLLDR in the php script.
I did not write the php program...

0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 35122195
Try removing the"ADDRESS_LIST":

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myHostname.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyInstance)))

Open in new window

0
 

Author Comment

by:toooki
ID: 35122427
I am using this command:
sqlldr login/pwd@myHostname.com/MyInstance control='/var/www/sqlload_control/load_changes_lib.ctl' log='/var/www/logs/load_changes_lib.log'

I am not using ADDRESS_LIST. The exact same command works on a Windows XP PC that has Oracle client s/w installed. And I am not here using tnsnames.ora entries.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 35123255
Check that your database is correctly started and there is no invalid parameters in the init<SID>.ora file.
0
 

Author Comment

by:toooki
ID: 35125422
The destination database is a production database and I can connect to that from my PC via sqlplus and even the below command "works" (populates the destination db) from my own PC (windows XP):

sqlldr login/pwd@myHostname.com/MyInstance control='/var/www/sqlload_control/load_changes_lib.ctl' log='/var/www/logs/load_changes_lib.log'

But the above command fails when I try from the Linux server.


0
 

Author Comment

by:toooki
ID: 35135522
It worked finally!! I was typing
ORACLE_HOME=/usr/prod/wsdcm/oraclient/linux/11g_64
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
The above in command prompt and then tried the sqlldr command.

But when I tried from the entire command plus the path/oracle home declaration from inside the file, it worked!!

Many thanks to you all for help. I got the direction ...
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

744 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

13 Experts available now in Live!

Get 1:1 Help Now