SQL*Loader Userid Length

[Scorby]

I find this somewhat astonishing but sql*loader 9.0.1.0.0/Oracle 9.2.0.6.0 seems to have an upper limit on the length of the username/password@dbname combination you pass in the userid parameter. If I try a combination with 30 characters, I get:

SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-00972: identifier is too long

Using a combination with a smaller number of characters and everything else the same works fine. Does anyone know if this is a known bug and if there is a workaround for it ? I've tried userid both on the command line and in a parameter file.

[tamilselvan]

I did not get an error when I used 30 chars length for user id and password in 9.2.0.5
Can you post your script?
Tamil

[Scorby]

It actually only seems to happen if direct path is used:

create table test_load (a number)

grant all on test_load to xxxxxxxxxxx

create public synonym test_load for test_load

Control file:

OPTIONS (
DIRECT=TRUE
)
LOAD DATA
APPEND
INTO TABLE TEST_LOAD
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (A)

Shell:

sqlldr userid=xxxxxxxxxxx/xxxxxxxxx@xxxxxxxx,control=test_load.ctl,log=test_load.log

Running the shell script gets:


SQL*Loader: Release 9.0.1.0.0 - Production on Tue Nov 22 10:09:13 2005

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-00972: identifier is too long

Remove the "direct=true" or switch to a username/password@db combination with only 21 characters and it works.

[tamilselvan]

Did you test with direct=true at the command line?

Tamil

[Scorby]

Just tried it and got the same error. I also tried defaulting my database using the ORACLE_SID and TWO_TASK variables, but got the same error with both of those as well.

[Scorby]

It appears the problem is not what I thought it was at all. The error message is extremely misleading. The problem actually appears to be that you can't run a direct path load against a table that the user doesn't own. When I tried the load with a shorter username, it still gave the same error. But when I created the table in that user's schema, it worked correctly.

[Scorby]

Actually, I think it's even simpler than that. It's because I was using a public synonym to access the table. Workaround seems to be to prefix the table with the username.