SQL Loader like errors in a table

Question

I have external tables. And I'd like to extract the data from those tables and insert/merge that data in other tables.

Now when a select from => insert into query or merge query runs then it is possible (and likely possible) that the data might be in a bad quality, which will result into breaking query. Say there is 000000 as date in external table which will result breaking query if I am merging data.

How can I log these errors in a table (for example) error table which will log the error, reason of error, line number and column name? Just like you see in SQL Loader logs. For example:

Record 2324: Rejected - Error on table AA_STAG_VR_01, column KS1.
ORA-01843: not a valid month

And the query shouldn't break upon rather. Rather log the error and move on like it happens in SQL Loader.

Is it possible? I tried to look around net but I wasn't unable to find anything, or maybe I simply don't know the magic words

Thanks in advance :-)

EDIT: Ok, I was able to solve the problem (well, partly) using the following approach.

CREATE TABLE error_table  (
   ora_err_number$ NUMBER, 
   ora_err_mesg$   VARCHAR2(2000),
   ora_err_rowid$  ROWID,   
   ora_err_optyp$  VARCHAR2(2),
   ora_err_tag$    VARCHAR2(2000) 
)

INSERT INTO destination_table (column)
SELECT column FROM external_table
LOG ERRORS INTO error_table REJECT LIMIT UNLIMITED

This gives me:

SELECT * FROM error_table;

----------------------------------------------------------------------------------------------------------------------------------------------------------
ORA_ERR_NUMBER$   |   ORA_ERR_MESG$                                                              |    ORA_ERR_ROWID$    | ORA_ERR_OPTYP$   | ORA_ERR_TAG$  |
----------------------------------------------------------------------------------------------------------------------------------------------------------

12899             |ORA-12899: value too large for column "SYSTEM"."destination_table"."column"

So far, so good. However, I would like to know what record number (line number in external_table) has this error. Because it is possible that fist 10 records went ok but 11th record was bad.

Answer 1

Check out FORALL + SAVE EXCEPTIONS clause. It might help you.

15:57:02 @> conn hr/hr@vm_xe                                                                  
Connected.                                                                                    
15:57:15 HR@vm_xe> create table err_test(unique_column number primary key);                   

Table created.                                                                                

Elapsed: 00:00:01.51                                                                          
15:57:46 HR@vm_xe> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('err_test', 'errlog');                

PL/SQL procedure successfully completed.                                                      

Elapsed: 00:00:00.46                                                                          
15:59:22 HR@vm_xe> insert into err_test select mod(rownum, 2) from dual connect by rownum < 10
16:00:00   2  log errors into errlog ('test') reject limit unlimited;                         

2 rows created.                                                                               

Elapsed: 00:00:00.87                                                                          
16:00:27 HR@vm_xe> commit;                                                                    

Commit complete.                                                                              

Elapsed: 00:00:00.00                                                                          
16:02:37 HR@vm_xe> col ora_err_mesg$ for a75                                          
16:02:43 HR@vm_xe> col unique_column for a10                                          
16:02:47 HR@vm_xe> select unique_column, ora_err_mesg$ from errlog;                   

UNIQUE_COL ORA_ERR_MESG$                                                              
---------- ---------------------------------------------------------------------------
1          ORA-00001: unique constraint (HR.SYS_C007056) violated                     
0          ORA-00001: unique constraint (HR.SYS_C007056) violated                     
1          ORA-00001: unique constraint (HR.SYS_C007056) violated                     
0          ORA-00001: unique constraint (HR.SYS_C007056) violated                     
1          ORA-00001: unique constraint (HR.SYS_C007056) violated                     
0          ORA-00001: unique constraint (HR.SYS_C007056) violated                     
1          ORA-00001: unique constraint (HR.SYS_C007056) violated                     

7 rows selected.                                                                      

Elapsed: 00:00:00.03                                                                  

Answer 2

Below some syntax, you have reject limit as in sqlloader, you have log files, bad files etc.

CREATE TABLE <table_name> (
<column_definitions>)

ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
[READSIZE <bytes>]
[SKIP <number_of_rows>
FIELDS TERMINATED BY '<terminator>'
REJECT ROWS WITH ALL NULL FIELDS
MISSING FIELD VALUES ARE NULL
(<column_name_list>))\
LOCATION ('<file_name>'))
[PARALLEL]
REJECT LIMIT <UNLIMITED | integer>;

See here some examples, and here the docs