2

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

jarlh
  • 42,561
  • 8
  • 45
  • 63
Shubha Parashar
  • 21
  • 4
  • I do not believe that there is a quick way to do this. you might have more success trying to merge the files outside of Oracle, and posting a new question with example contents of the 2 files and your potential code under either "powershell" or "bash" on here, depending on your operating system – Jad Feb 20 '21 at 11:22

2 Answers2

1

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.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • That is the clients requirement. Not my expectation. :) I am new to SQL loader. Wanted to know if something like this exists already. – Shubha Parashar Feb 21 '21 at 12:45
1

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.
Vahram Danielyan
  • 187
  • 3
  • 11
  • Hi, I am already using a shell script to insert data into my table. Also, all the success records need to be added. So yes i have ignored error . Wanted to know if there is any existing way to do so. Also, in my case i can have multiple input files in one go. The log currently has syntax similar to below: Record no 3 invalid date. this comes per input file. The client wants individual error against each record. – Shubha Parashar Feb 21 '21 at 12:42
  • How is coming the data? By position? or by separator? – Vahram Danielyan Feb 21 '21 at 18:51
  • By delimiter ";" – Shubha Parashar Mar 10 '21 at 12:53
  • So do two tasks: 1.- Create a Table as . put all data type as a big VARCHAR2 to has space for bad registers 2.- Use a CTL the same you have to load the original table. If you can register information into your TABLE_ERR, you must do MERGE to insert the error value into the column_error of the original table. – Vahram Danielyan Mar 10 '21 at 13:03