Solved

connection string in the Oracle sqlldr command

Posted on 2011-03-02
11
5,905 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 69

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
 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Creating and Managing Databases with phpMyAdmin in cPanel.
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
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.

680 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