Re: Confused SQL*Loader

[Quoted] On Mon, 10 Mar 2003 15:16:02 -0600, TurkBear <john.greco_at_dot.state.mn.us> wrote:

>Watson Davis <thepencilneck_at_yahoo.com> wrote:
>
>>I've got a machine with two Oracle 7.3.4 instances. One of the
>>instances is old and one of the instances is new.
>>
>>When I try to load data into the new instance, SQL*Loader tries to use
>>the old instance... actually, it DOES use the old instance if it can.
>>My ORACLE_SID is pointing to the new instance.
>>
>>SQL*Loader does this for two different oracle accounts. Both of these
>>accounts exist in both instances. One account has a different
>>password and one account has the same password. For the account with
>>a different password, I get the error:
>>
>>SQL*Loader: Release 7.3.4.0.1 - Production on Mon Mar 10 10:03:00 2003
>>
>>Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
>>
>>SQL*Loader-925: Error while logging on to ORACLE (ulmconnect)
>>ORA-01017: invalid username/password; logon denied
>>
>>But in this same session without changing anything, I log into
>>SQL*Plus with no problems.
>>
>>For the account with the same password, SQL*Loader loads the table...
>>but like I said, in the wrong instance.
>>
>>It's very weird. I've even tried specifying the database to use with
>>syntax similar to:
>>
>>sqlload userid=username_at_instance, control=loadtest.ctl,
>>data=testdata.dat, rows=1000
>>
>>And I get the error:
>>SQL*Loader: Release 7.3.4.0.1 - Production on Mon Mar 10 11:46:08 2003
>>
>>Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
>>
>>SQL*Loader-925: Error while logging on to ORACLE (ulmconnect)
>>ORA-12154: TNS:could not resolve service name
>>
>>But if I do something like:
>>
>>sqlplus username_at_instance
>>
>>I can log in fine.
>>
>>Anyone have any ideas? BTW, this is all in Solaris.
>>
>>TIA.
>>
>>Watson (the pencil neck) Davis
>
>Try without the = sign..
>
>Sqlload user/password_at_instance control=loadtest.ctl etc..

Well... the userid=blahblahblah has worked with other loader scripts for the past 10 years AND works with this script as long as I'm trying to load into the old database.

But.

I went ahead and tried it your way just to check... and got the same error.

>
>
>( The Data file should be specified in the ctl file...)

Um, no.

The datafile CAN be specified in the control file but if you're in a situation where the name of the datafile changes, then you can put the name of the datafile on the command line and pass it in as a variable.

AND, if you put user/password, then you've just published that password to anyone who can do a "ps" on that system. If you just put the user name, then sqlload asks for the password and you can either type it in manually or put the password in a file and redirect it in with "< pfile". This is a much more secure way to set things up.

Thanks, though.

Watson (the pencil neck) Davis Received on Mon Mar 10 2003 - 22:51:07 CET