Results 1 to 9 of 9
  1. #1
    Join Date
    May 2002
    Posts
    62

    Question Unanswered: Strange SQL Loader problem

    Strange SQL Loader problem.

    We are encountering a strange SQL Loader problem.

    Here are the details. We are using the following oracle version.

    Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
    OS - Unix IBM AIX

    The sqlldr is invoked from within a Unix shell script using the following command.

    sqlldr PARFILE=$conn_file data=$Dat control=$Ctl log=$Log bad=$Bad discard=$Dsc errors=999 direct=true >/dev/null 2>/dev/null

    We are loading multiple files at the same time into one table.

    After loading few files, SQL Loader says "Rows not loaded due to data errors." No other error. Please see below.

    74265 Rows successfully loaded.
    0 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.

    39138 Rows successfully loaded.
    1000 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.

    Errors in Load

    0 Rows successfully loaded.
    400 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.

    Errors in Load

    Same with all other files followed after this.

    Can anyone explain me what's going on here.

    Thanks
    -Bheem

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    duplicate rows?
    what do your logs say? discard file? etc.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2002
    Posts
    62
    No duplicate rows. No other error or message. Entire file is not loaded. Data is good.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by bheemsen
    We are loading multiple files at the same time into one table.
    Maybe the "at the same time" is the problem? Are you using direct path load or conventional?

    I'm a bit surprised that there aren't more messages in the log file. Can you spot anything in the .bad file?

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    400 Rows not loaded due to data errors
    400 is a nice, round number. Is there a discarded records limit number set using DISCARDS or DISCARDMAX in your control file?
    Data is good
    Well, if Oracle says data isn't good, then it is not. It doesn't behave like people usually do (today I'm more than happy with my T-shirt, while tomorrow it will be discarded into trash). Or, to put it another way: perhaps input data IS good, but control file treats them in a wrong manner?

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    obviously your LOG FILES are NOT good.
    If they were good then you would get logs with information in them.

    I would start by removing all the parameter passing for your logs and hard code it. Then run the scripts to see if your log parameters might be a little out of whack.

    Also, if you provide your script, .ctl file, what parameters you pass,
    sample data that got loaded vs. sample data that DID get loaded etc.
    This is obviously where something is going wrong.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    am I missing something? Why do you have a PAR file and a CONTROL file?
    is the parfile your username/password@sid connector?

    considering you set and pass so many parameters I wouldn't mind seeing
    exactly what you are passing
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    "No other error or message."
    what about the message "1000 Rows not loaded due to data errors."

    "Data is good."
    clearly not. see above.

    you specify "errors=999" on the sqlloader command line. this means that processing will allow 999 errors. on error #1000, processing will stop, and no further records will attempt to load. it seems that you are getting exactly what you asked for.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  9. #9
    Join Date
    Sep 2006
    Posts
    1

    Strange SQL Loader problem

    There is a data problem... You should be able to look in your directory where bad records are to be put. You will be able to look at the data and see where the problem potentially is.

Posting Permissions

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