Solved

connection string in the Oracle sqlldr command

Posted on 2011-03-02
11
6,086 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 70

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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

751 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