Inserting the rows from source_table to the target_table using stored proc and the inserting query,

insert into target_table (column_1, column_2) 
select (column_1, column_2)
from source_table
log error into error_table ("Inserting into error table")
reject limit unlimited

Having the below columns in the error_table with the data columns for the particular row,

ORA_ERR_NUMBER$
ORA_ERR_MESG$
ORA_ERR_ROWID$
ORA_ERR_OPTYP$
ORA_ERR_TAG$

During the inserting if the error occurred inserts Oracle error number and error message. Is there any approach to get the errored column name ?

Inserting the rows from source_table to the target_table using stored proc and the inserting query,

insert into target_table (column_1, column_2) 
select (column_1, column_2)
from source_table
log error into error_table ("Inserting into error table")
reject limit unlimited

Having the below columns in the error_table with the data columns for the particular row,

ORA_ERR_NUMBER$
ORA_ERR_MESG$
ORA_ERR_ROWID$
ORA_ERR_OPTYP$
ORA_ERR_TAG$

During the inserting if the error occurred inserts Oracle error number and error message. Is there any approach to get the errored column name ?

• oracle-database
• oracle11g
• oracle19c

• Many (most?) errors aren't going to have a 1:1 mapping to a particular column. You may be violating a unique constraint created on a combination of multiple columns, you may have constraints that enforce relationships involving multiple columns, you may have triggers that error out due to bugs in PL/SQL code, etc. In general, relatively few errors tie back unambiguously to a particular column. – Justin Cave Commented Mar 18 at 17:56

1 Answer 1

I don't believe that the DML errors table that dbms_errlog.create_error_log creates will store the full error stack with line and column numbers. What you need is the line and column number from the originating exception.

Pull your DML statement out of PL/SQL and run it as a standalone SQL statement, manually, without the log errors... clause. The exception raised should include a line and column (position from the left margin along the line specified) which should point exactly to one of your column names as your SQL is formatted.

insert into target_table(id, value_1, value_2) values (1,3,'b');

Result in my client:

My SQL was on line 18 of my window, and column 61 lines up with the ' in 'b', telling me that it was that value that caused the issue. You only get this info when running standalone SQL. Running SQL within PL/SQL won't provide it. The moment you place this in a PL/SQL block, you'll get the position of the start of the SQL, not the offending column, so it's not very helpful beyond knowing which SQL failed. Apparently the log errors... doesn't even try to provide even that much.

While I'm sure there's a use case for using that mechanism, my suggestion is to avoid it. You generally want to program in such a way that an error is brought to your attention, demanding a decision about how to handle it, not swallowed without a trace and it's left to you to query a special table to find out if your insert worked. Instead, create a cursor loop from your source table and do a single row insert into the target table. Use exception handlers to handle/report any errors.