Results 1 to 4 of 4

Thread: sql loader

  1. #1
    Join Date
    Nov 2003

    Unanswered: sql loader

    Hello friends

    I am using sql loader. I want that either all of my records should get loaded or nothing should load. If there is error in any of the record the loaded data should rollback and nothing should be loaded. What option of sqlloader should I use to achieve this.

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    This is untested (I'm not sure would ROWS commit anything or not), but you might consider combination of those options:

    Limit the number of discarded records using the DISCARDMAX keyword. To stop on the first discarded record, use DISCARDMAX 1.

    Use the direct path load: ROWS keyword identifies the number of rows you want to read from the datafile before data save. The default is to read all rows and save data once at the end of the load.

    Here is SQL*Loader documentation; spend some time and read it - maybe you'll find the correct answer if my suggestion won't work.


    I forgot to mention that another option use of external tables instead of SQL*Loader, if possible. Doing so, you could easily trap errors and either COMMIT or ROLLBACK, depending on the insert result.

  3. #3
    Join Date
    Jun 2003
    West Palm Beach, FL


    You could also try the INTO TABLE <table> TRUNCATE option to replace all data after an erroneous load.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jul 2003
    just increase the commit buffer to the maximum rows to load and
    then do what LF said to lower discardmax to 1
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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