“Overflow” is an option used for error handling.
You cannot expect quality(valid) data everytime, sometimes we might get bad rows also, hence an error would occur while loading the data. To handle such kind of rows “Use overflow file” is the best option.
EX:- Suppose you have a unique constraint on target table, you need to load only distinct records, but your’e getting duplicate records from source, you want to reject these duplicate records into a file.
Now, you can capture those erros by selectng “Use overflow file” in target table options tab. Errors will be logged or written to a file in a specified location.
For File name, you can specify like this,
Filename : C:\Reject_records.txt
There are two options avialbe in File Format Write_data and Write_sql
- If you select write_data, data will be written in normal format along with the error number & opcode
- If you select write_sql, data will be written as SQl insert statements in your specified location
The beauty of this option is, job doesn’t terminate amidst execution when an error occurs. Error will be handled by this option and all the bad rows were captured and logged into a file. Monitor window displays “job execution is completed successfully” , and error log displays the error cause & bad rows.
you can see rej_rec log also, the bad rows were written into the overflow file.
If there is no error then the overflow file will be empty at the specified location