Loading csv and writing bad records with individual errors

user_8068982 0
Viewed 278

I am loading a csv file into my database using SQL Loader. My requirement is to create an error file combining the error records from .bad file and their individual errors from the log file. Meaning if a record has failed because the date is invalid, against that record in a separate column of error description , Invalid date should be written. Is there any way that SQL loader provides to combine the too. I am a newbie to SQL loader. Database being used Oracle 19.c

sql-loader oracle
2 Answers

You might be expecting a little bit too much of SQL*Loader.

How about switching to external table? In the background, it still uses SQL*Loader, but source data (which resides in a CSV file) is accessible to you by the means of a table.

What does it mean to you? You'd write some (PL/)SQL code to fetch data from it. Therefore, if you wrote a stored procedure, there are numerous options you can use - perform various validations, store valid data into one table and invalid data into another, decide what to do with invalid values (discard? Modify to something else? ...), handle exceptions - basically, everything PL/SQL offers.

Note that this option (generally speaking) requires the file to reside on the database server, in a directory which is a target of Oracle directory object. User which will be manipulating CSV data (i.e. the external table) will have to acquire privileges on that directory from the owner - SYS user.

SQL*Loader, on the other hand, runs on a local PC so you don't have to have access to the server itself but - as I said - doesn't provide that much flexibility.

user_90979060

it is hard to give you a code answer without the example.

If you want to do your task I can suggest two ways.

  • From Linux. If you loaded data and skipped the errors, you must do two executions. That is not an easy way and not effective.
  • From Oracle. Create a table with VARCHAR2 columns with the same length as in the original. Load data from bad_file. Convert your CTL adapted to everything. And try to load in the second table. Finally MERGE the columns to original.
user_128415580
Related