OraFAQ Forum: Server Utilities » [SQL*Loader] Problems handing over connect string

Home » RDBMS Server » Server Utilities » [SQL*Loader] Problems handing over connect string (Oracle 10g on Win32 using Cygwin)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

[SQL*Loader] Problems handing over connect string [message #318598]

Wed, 07 May 2008 04:41

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

Barbara Boehmer
Messages: 9094
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

Barbara Boehmer
Messages: 9094
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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 05:40:04 CDT 2024