Thanks for the question, Ami.
Asked: April 07, 2016 - 1:37 pm UTC
Last updated: October 08, 2021 - 1:16 am UTC
Viewed 1000+ times
Thank you. Your blogs are really helpful.
I have a specific requirement while capture the exception that might occur at the time of bulk insert. What I need to do is when an exception has occurred, while updating the information in the exception table I need to concatenate all the fields from my staging table into a single column as oppose to the have each and every column from staging table into exception table. Below is the example of the stored procedure. So just to make it more clear after the mandatory columns in the exception table I need a column which would have all the fields from AP_ITME table into a single column in error table.
Please let me know if this is possible.
create or replace procedure AP_ITEM_ADD_CHANGE(ITEM_IN IN AP_ITEM_ARRAY)
FORALL indx in 1 .. ITEM_IN.COUNT
insert /*+ APPEND */ into AP_ITEMS ("ITEM_ID","ITEM_NAME","SERIAL","ADDED_ON")
LOG ERRORS INTO err$_AP_ITEM
REJECT LIMIT UNLIMITED;
create table err$_AP_TIEM (ora_err_number$ number, ora_err_mesg$ varchar2(2000),ora_err_rowid$ rowid, ora_err_optyp$ varchar2(2),
ora_err_tag$ varchar2(2000), <How to get the concatenate all the column from AP_ITEM table>);
and we said...
One means of doing that, would be use BULK_EXCEPTIONS rather than dml error logging. That way, you will have explicit control over the elements.
Take a look here at the bulk processing articles in https://asktom.oracle.com/magazine-archive.htm
for an example.
Otherwise, you'd need to do it in a two-step process, ie, save the columns as they normally are (ie separate) via the error logging table, and then query that to concatenate the columns into a single result.
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment