-
SQL*Loader Userid Length
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.
-
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
-
It actually only seems to happen if direct path is used:
Code:
create table test_load (a number)
grant all on test_load to xxxxxxxxxxx
create public synonym test_load for test_load
Control file:
Code:
OPTIONS (
DIRECT=TRUE
)
LOAD DATA
APPEND
INTO TABLE TEST_LOAD
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (A)
Shell:
Code:
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.
-
Did you test with direct=true at the command line?
Tamil
-
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.
-
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.
-
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.