Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » RDBMS Server » Server Utilities » [SQL*Loader] Problems handing over connect string (Oracle 10g on Win32 using Cygwin)
[SQL*Loader] Problems handing over connect string [message #318598] Wed, 07 May 2008 04:41 Go to next message
houseangel
Messages: 2
Registered: May 2008
Junior Member
Hi @ll,

I am actually trying to import some data to my Oracle 10g. This has to work automatically via ANT (calling the command via exec) and has to be configured via a property file for each machine (the ANT task should only rely on having a Oracle 10g installed).
What I am trying to do is to call the SQL*Loader and to hand over the complete connect string somehow, not relying that it is in the tnsnames ... My problem: this doesn't work as I expexted:

Attempt 1: Connect String via command line
sqlldr userid=user/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))(CONNECT_DATA=(SID=SID))) control=<PATH> data=\"-\"


The error message in this case: my Cygwin complains that a '(' is incorrect (bash: Syntax Error near unexpected token '('). If I quote the whole thing THE SQL*Loader complains, that a parameter isn't correct (SQL*Loader-100: syntax error ...) Ok - how about an alternative to command line? I googled and found:

Attempt 2: Connect String in Parfile
Parfile:
USERID=user/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))(CONNECT_DATA=(SID=SID)))
CONTROL=<PATH>
DATA="-"


The call:
sqlldr parfile=parfile.par


Doesn't work much better Sad
LRM-00116: Syntaxfehler bei 'user/pa' im Anschluss an '='
LRM-00113: Fehler beim Verarbeiten der Datei 'parfile.par'
...

(The english error message would be something like:
LRM-00116: syntax error in 'user/pa' after '='
LRM-00113: Error processing the file 'parfile.par'
...)

Does anyone have an idea what I am doing wrong or how i may solve this problem easier?

Thanks in advance
Regards Angel
Report message to a moderator
Re: [SQL*Loader] Problems handing over connect string [message #318692 is a reply to message #318598 ] Wed, 07 May 2008 10:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 3939
Registered: November 2002
Location: California, USA
Senior Member
I presume that what you posted is pseudo-code and not what you actually entered. It is much better to post a copy and paste of an actual run. Otherwise, it is difficult to guess where the problems are. In general, sqlldr is very particular about spaces. You cannot have any spaces before or after the equal signs. Any paths that contain spaces must be enclosed within double quotes or the shorter pathname used. Here is an example:


C:\>C:\app\Barbara\product\11.1.0\db_1\BIN\SQLLDR USERID=scott/tiger@orcl CONTROL="c:\oracle11g\test.ctl" LOG="c:\oracle11g\test.log" DATA="c:\oracle11g\test.dat"

SQL*Loader: Release 11.1.0.6.0 - Production on Wed May 7 08:30:13 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Commit point reached - logical record count 1

C:\>
Report message to a moderator
Re: [SQL*Loader] Problems handing over connect string [message #318697 is a reply to message #318598 ] Wed, 07 May 2008 11:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 3939
Registered: November 2002
Location: California, USA
Senior Member
Here is an example using a parfile:

-- contents of c:\oracle11g\parfile.par:
USERID=scott/tiger@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Barbara-PC)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl))) "
CONTROL="c:\oracle11g\test.ctl"
LOG="c:\oracle11g\test.log"
DATA="c:\oracle11g\test.dat"


-- load:
C:\>C:\app\Barbara\product\11.1.0\db_1\BIN\SQLLDR PARFILE="c:\oracle11g\parfile.par"

SQL*Loader: Release 11.1.0.6.0 - Production on Wed May 7 09:08:55 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Commit point reached - logical record count 1

C:\>
Report message to a moderator
Re: [SQL*Loader] Problems handing over connect string [message #318698 is a reply to message #318598 ] Wed, 07 May 2008 11:13 Go to previous message
houseangel
Messages: 2
Registered: May 2008
Junior Member
I was able to solve the problem myself in the mean ... actually it was a problem that had nothing to do with SQL*Loader. My database was not correctly started due to an invalid init<SID>.ora file and the SID also was not listed in the listener.ora file.

Now after that is done the connection using the connect string in the parfile works fine:

The only thing for now is, that i cannot use input from stdin (using data=\"-\") due to an oracle bug in 10.2 as described here:

http://forum.cloveretl.org/viewtopic.php?t=338&sid=519c030bfe8dcc3174af6412cd00e9be

... but thats another topic.

[Updated on: Wed, 07 May 2008 11:14]

Report message to a moderator
Previous Topic:importing problem
Next Topic:Reg Exporting a instance from the DB
Goto Forum:
  

[ Syndicate this forum (XML) ] [ ]

Current Time: Fri May 16 11:15:41 CDT 2008

Total time taken to generate the page: 0.02105 seconds
.:: Forum Home :: Site Home :: Wiki Home :: Contact :: Privacy ::.