Call Sqlldr from DOS Batch.
PowerOne
I'm getting error when I try to call Sqlldr from a batch file:
My batch file look like this:
SQLLDR PARFILE=C:\TMP\mypar.par CONTROL=C:\TMP\mycontrol.txt ........
Someone told to set Oracle Var. but anyway the problem persist.
This the error message:
Message 2100 not found; No message file for product=RDBMS,
facility=ULMessage 2100 not found; No message file for product=RDBMS,
facility=UL
Note: The Oracle database in a machine different where I'm running the
batch.
fitzj...@cox.net
But you DO have your ORACLE_HOME variable properly set?
It would appear not.
David Fitzjarrell
PowerOne
set $ORACLE_HOME=C:\ORANT\BIN
set $ORACLE_SID=DBNAME
I missed something? or Am I doing something wrong?
Laurenz Albe
This looks completely wrong.
- Using "set $VAR" to set variable VAR is wrong on every shell I know.
Including MS/DOS.
- C:\ORANT\BIN sounds very much like the wrong location.
Is C:\ORANT\BIN the directory that contains "sqlldr.exe"?
Then you should try:
set ORACLE_HOME=C:\ORANT
set ORACLE_SID=DBNAME
Check that the environment variables are set currectly by using "set".
Yours,
Laurenz Albe
Doug Miller
>Hi,
>
>I'm getting error when I try to call Sqlldr from a batch file:
>
>My batch file look like this:
>SQLLDR PARFILE=C:\TMP\mypar.par CONTROL=C:\TMP\mycontrol.txt ........
What does the *rest* of this command look like? And what are the contents of
C:\TMP\mypar.par ?
Doug Miller
Yes, and yes. This is incorrect syntax for setting environment variables in
MS-DOS. Get rid of the dollar signs.
Doug Miller
>Check that the environment variables are set currectly by using "set".
'set' with no parameters displays the values of *all* environment variables;
this is potentially a fairly large set. The values of individual variables can
be displayed with 'echo %varname%' e.g.
echo %ORACLE_HOME%
PowerOne
The echo of these var. say:
echo %ORACLE_HOME%
D:\oracle\ora92\bin
echo %ORACLE_SID%
DBNAME
This mean that I don't have to set it.
This is the complete cmd:
sqlldr username/pass...@mydbname.world control=D:\loader.txt
log=mylog.log bad=mybad.bad errors=1000000 direct=true
The parfile is used to hide the user and password.
fitzj...@cox.net
> On Mar 25, 7:13 am, spamb...@milmac.com (Doug Miller) wrote:
>
> > In article <1206436364.771...@proxy.dienste.wien.at>, Laurenz Albe <inv...@spam.to.invalid> wrote:
> > >Check that the environment variables are set currectly by using "set".
>
> > 'set' with no parameters displays the values of *all* environment variables;
> > this is potentially a fairly large set. The values of individual variables can
> > be displayed with 'echo %varname%' e.g.
>
> > echo %ORACLE_HOME%
>
> The echo of these var. say:
> echo %ORACLE_HOME%
> D:\oracle\ora92\bin
> echo %ORACLE_SID%
> DBNAME
> This mean that I don't have to set it.
> This is the complete cmd:
> log=mylog.log bad=mybad.bad errors=1000000 direct=true
>
> The parfile is used to hide the user and password.
What does the following command report:
tnsping mydbname.world
It may be that your tnsnames.ora file is misconfigured or missing.
David Fitzjarrell
Doug Miller
>On Mar 25, 7:13=A0am, spamb...@milmac.com (Doug Miller) wrote:
>> In article <1206436364.771...@proxy.dienste.wien.at>, Laurenz Albe <inv...=
>@spam.to.invalid> wrote:
>> >Check that the environment variables are set currectly by using "set".
>>
>s;
>> this is potentially a fairly large set. The values of individual variables=
> can
>> be displayed with 'echo %varname%' e.g.
>>
>> echo %ORACLE_HOME%
>
>The echo of these var. say:
> echo %ORACLE_HOME%
> D:\oracle\ora92\bin
> echo %ORACLE_SID%
> DBNAME
>This mean that I don't have to set it.
Well, *maybe* it means that. Are those the correct values? (In your original
post, you indicated you were trying to set ORACLE_HOME to C:\ORANT\BIN, not
D:\oracle\ora92\bin as shown here. It's not clear which of these, if either,
is correct on your system.)
PowerOne
actual version is
D:\oracle\ora92\bin, I made the correction.
This is the result :
D:\oracle\ora92\bin>tnsping mydbname.WORLD
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on
25-MAR-20
08 10:11:18
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Message 3511 not found; No message file for product=NETWORK,
facility=TNSTNS-035
05: Message 3505 not found; No message file for product=NETWORK,
facility=TNS
Shakespeare
"Doug Miller" <spam...@milmac.com> schreef in bericht
news:OV5Gj.36056$J41....@newssvr14.news.prodigy.net...
The oracle home setting should not include the bin directory, so ORACLE_HOME
should be set to D:\oracle\ora92 in this case.
Shakespeare
Malcolm Dew-Jones
: echo %ORACLE_HOME%
More useful is the set command itself (I'm on XP here, I dont remember
when this started to work)
set ORACLE_HOME shows this variable
set O shows all variables starting with O
And filtering also works
set | findstr /i "bin"
PowerOne
OK.
And I executed the Sqlldr and the data was Uploaded.
But my problem now is to hide the user and password from the batch
file.
When I write the user and password after the sqlldr command it works
fine but
I need to put the user an password in another file like this:
SQLLDR parfile=D:\myfile.par control=D:\myloader.txt log=mylog.log
bad=mybad.bad errors=1000000 direct=true
This is the message displayed:
LRM-00110: syntax error at 'connect'
LRM-00113: error when processing file 'D:\mypar.par'
SQL*Loader: Release 9.2.0.1.0 - Production on Tue Mar 25 14:10:46 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL*Loader-100: Syntax error on command-line
fitzj...@cox.net
I'll ask this again:
Will you POST the contents of your parfile??
David Fitzjarrell
PowerOne
>
> Will you POST the contents of your parfile??
No, I only need to store the user/password@dbname in another file. Is
this possible in this way?
Laurenz Albe
>>> I need to put the user an password in another file like this:
>>> SQLLDR parfile=D:\myfile.par control=D:\myloader.txt log=mylog.log
>>> bad=mybad.bad errors=1000000 direct=true
>>> This is the message displayed:
>>> LRM-00110: syntax error at 'connect'
>>> LRM-00113: error when processing file 'D:\mypar.par'
>>> SQL*Loader: Release 9.2.0.1.0 - Production on Tue Mar 25 14:10:46 2008
>>> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>>> SQL*Loader-100: Syntax error on command-line
>>
Nobody can tell you what you did wrong unless you tell us what you did.
Should be obvious.
As things are, all I can tell you is: use the correct syntax, and it will
work. So the answer to your question is "yes".
Yours,
Lauernz Albe
Doug Miller
How do you expect anyone to be able to help you, when you refuse to provide
all the relevant information?
PowerOne
This is the content of parfile:
connect username/pass...@dbname.world
fitzj...@cox.net
> Sorry, I misundertood the question.
>
> This is the content of parfile:
And your problem is with your syntax; you cannot use the 'connect'
keyword in a parfile. You need to use this instead.
userid=username/pass...@dbname.world
And it will work.
David Fitzjarrell
PowerOne
it work perfect.
Thank you all.