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 1000+ 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 Chris 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)

Is this answer out of date? If it is, please let us know via a Comment

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Utilities

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