SQL*Loader Userid Length
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQL*Loader Userid Length

  1. #1
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #3
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Did you test with direct=true at the command line?

    Tamil

  5. #5
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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.

  6. #6
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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.

  7. #7
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width