Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

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

Comments

Exception Bulk Insert

Ami Paradkar, April 12, 2016 - 1:43 pm UTC

Thank you for response it was very helpful

Dieter Lohrstr├Ąter, October 07, 2021 - 11:20 am UTC

Tom, your link is not valid anymore...
Connor McDonald
October 08, 2021 - 1:16 am UTC

Thanks - we've archived some links here

https://asktom.oracle.com/magazine-archive.htm

Check out the BULK processing ones.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library