Re: Confused SQL*Loader

From: Henk Hultink <hhu_at_stopspam.stoas.nl>
Date: Fri, 14 Mar 2003 12:28:56 +0100
Message-ID: <3e71bcfb_at_news.wau.nl>


[Quoted] Could you be more specific as to what phase the moon should be in for this problem to occur... ;-) Just curious!

Henk

"Watson Davis" <thepencilneck_at_yahoo.com> wrote in message news:17FB73A715EE6E9C.06EEE3BF8162C984.2A9C99D298F30ABB_at_lp.airnews.net...
> OK.
>
> I talked to Oracle and they were able to suggest a fix.
>
> The problem is that there is a bug in SQL*Loader in versions prior to
> 8.1.something. It doesn't happen all the time but occasionally when
> the phase of the moon is just right, SQL*Loader has a memory problem
> that causes it to misread the command line and environment variables
> and just get all confused in general.
>
> There are TWO ways to fix this. At least, there are two ways that
> worked for me.
>
> The first is to remove the username/password from the script/command
> line and then feed the <username>/<password>_at_<instance> when it asks
> for username. For example, when the bug exists for whatever reason:
>
> sqlload user/passwd ...
> ...won't work.
>
> sqlload user/passwd_at_instance ...
> ...won't work.
>
> sqlload ...
> username: user/passwd
> ... won't work.
>
> sqlload ...
> username user
> password: passwd
> ... won't work.
>
> sqlload ...
> username: user/passwd_at_instance
> ... works.
>
> OR.
>
> You can set TWO_TASK to the ORACLE_SID you're wanting to use. Then
> the script works fine with the username and password wherever you want
> to put it.
>
> So.
>
> There you have it.
>
> I still don't know why it was loading into the old database unless the
> but was causing it to read the tnsnames.ora file and pick up the first
> database it found. Or something.
>
> Watson (the pencil neck) Davis
>
> On Mon, 10 Mar 2003 15:42:16 -0500, 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
>
Received on Fri Mar 14 2003 - 12:28:56 CET

Original text of this message