Thanks for the question, Ami.
Asked: April 07, 2016 - 1:37 pm UTC
Last updated: October 08, 2021 - 1:16 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi Tom,
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)
AS
begin
FORALL indx in 1 .. ITEM_IN.COUNT
insert /*+ APPEND */ into AP_ITEMS ("ITEM_ID","ITEM_NAME","SERIAL","ADDED_ON")
values(ITEM_IN(indx).ITEM_ID,ITEM_IN(indx).ITEM_NAME,ITEM_IN(indx).SERIAL,ITEM_IN(indx).ADDED_ON)
LOG ERRORS INTO err$_AP_ITEM
REJECT LIMIT UNLIMITED;
COMMIT;
END;
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 Connor 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.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment