Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

connection string in the Oracle sqlldr command

Posted on 2011-03-02
11
Medium Priority
?
7,153 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 71

Accepted Solution

by:
Qlemo earned 1800 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
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

885 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