Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

connection string in the Oracle sqlldr command

Posted on 2011-03-02
11
Medium Priority
?
6,808 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

721 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