Re: Confused SQL*Loader

OK.

[Quoted] 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 Tue Mar 11 2003 - 18:31:10 CET