Skip to Main Content
  • Questions
  • Dynamically set the error limit for sql*loader

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sirisha.

Asked: February 18, 2021 - 2:22 pm UTC

Last updated: February 19, 2021 - 1:39 pm UTC

Version: 19

Viewed 100+ times

You Asked

Hi

Is it possible to set the ERRORS parameter for sqlldr dynamically i.e. based on the number of rows in the file. I want the sqlldr to capture all the errors.

Thank you
Sirisha

and we said...

As the docs say:

To specify that all errors be allowed, use a very high number.

https://docs.oracle.com/en/database/oracle/oracle-database/21/sutil/oracle-sql-loader-commands.html#GUID-6DAE53EE-0A36-478C-B7C4-5C5F75E4B134

:)

Any reason for you not to use:

errors=9999999999999999999999999999


?

That's 9 octillion; I don't think there's any database in the world with that many rows in a single table!

If you insist on capping the errors to (roughly) the number of records in the file, you could write a shell script that:

- Counts the number of lines in the source file
- Passes that to a SQL*Loader script/command as the number of errors to allow

...but that seems like a lot of effort when you could use a huge number like the above.

Rating

  (1 rating)

Comments

A reader, February 19, 2021 - 11:52 am UTC

Yes , it’s fine ,I can use that big number. But I thought the default maximum value for ERRORS parameter is 2147483647.

I will try using that big number and test the scripts.

Thank you.
Chris Saxon
February 19, 2021 - 1:39 pm UTC

The documentation doesn't specify an upper limit; the 9 octillion worked for me

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.