tag:blogger.com,1999:blog-6470544856392243336.post5399635274010912651..comments2024-02-29T00:53:27.879-08:00Comments on It's All About ORACLE: Handling FORALL Exceptions with the %BULK_EXCEPTIONS AttributeHIMANSHU KARKIhttp://www.blogger.com/profile/03184024429782462501noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-6470544856392243336.post-46133165527068578682014-10-30T11:16:16.435-07:002014-10-30T11:16:16.435-07:00Hi Taoqueer,
Sorry couldn't saw your post earl...Hi Taoqueer,<br />Sorry couldn't saw your post earlier.<br /><br />BULK EXCEPTION is for allowing a continuous DML operation even if some records raise exception.<br /><br />What you are requesting is different. I have just tried to perform what you are asking:<br /><br />create table test_tomkt_raw<br />(c1 varchar2(20),<br />c2 varchar2(20),<br />c3 varchar2(20));<br /><br />create table test_mkt<br />(c1 varchar2(20),<br />c2 varchar2(20),<br />c3 varchar2(20));<br /><br />create table test_mkt_log<br />(c1 varchar2(20),<br />c2 varchar2(20),<br />c3 varchar2(20));<br /><br /><br />insert into test_tomkt_raw VALUES( 'A','B','C');<br />insert into test_tomkt_raw VALUES( 'A','B','C');<br />insert into test_tomkt_raw VALUES( 'D','E','F');<br />insert into test_tomkt_raw VALUES( 'R','BD','AC');<br />insert into test_tomkt_raw VALUES( 'AQ','SB','AC');<br />insert into test_tomkt_raw VALUES( 'AA','BA','CA');<br />insert into test_tomkt_raw VALUES( 'A','B','C');<br />insert into test_tomkt_raw VALUES( 'D','E','F');<br /><br /><br />ALTER TABLE test_mkt ADD PRIMARY KEY ON (C1,C2,C3);<br /><br />create or replace procedure p_insert_mkt<br />is<br />exc_e1 EXCEPTION;<br />PRAGMA EXCEPTION_INIT(exc_e1, -1);<br /><br />cursor c1 is select c1,c2,c3 from test_tomkt_raw;<br />r1 test_mkt%rowtype;<br />begin<br /><br />open c1;<br />loop<br />fetch c1 into r1;<br /><br />if c1%found then<br /><br /> begin <br /> insert into test_mkt (c1,c2,c3)<br /> select r1.c1, r1.c2, r1.c3 from dual;<br /> <br /> exception<br /> when exc_e1 THEN<br /> insert into test_mkt_log ( c1,c2,c3)<br /> select r1.c1, r1.c2, r1.c3 from dual;<br /> <br /> end;<br /><br />end if;<br /><br />end loop;<br /><br />commit;<br /><br />end p_insert_mkt;<br /><br />I have made the procedure for handling duplicate record handling.<br /><br />You can consider this and make the procedure are per your need.<br /><br />I hope it helped you. HIMANSHU KARKIhttps://www.blogger.com/profile/03184024429782462501noreply@blogger.comtag:blogger.com,1999:blog-6470544856392243336.post-32326019709462579112014-04-03T23:17:34.110-07:002014-04-03T23:17:34.110-07:00Hi,
How do we know the exact record on which we g...Hi,<br /><br />How do we know the exact record on which we got the error?<br /><br />Suppose you are inserting millions of records in chunk and on some of the records throw Unique/Primary key violation error. In this case we have to update such problematic records. like<br /><br />DECLARE<br />v1 number;<br />v2 varchar2(20);<br />v3 date;<br />v4 varchar2(20);<br />BEGIN<br /> FOR I In (SELECT a, b, c, d from sometable where k = 'something') LOOP<br /> Begin<br /> Insert into thistable ( col1, col2 , col3, col4) values (i.a, i.b, i.c , i.d );<br /> EXCEPTION<br /> WHEN DUP_VAL_ON_INDEX THEN<br /> UPDATE thistable<br /> SET col3 = i.c, <br /> col4 = i.d<br /> WHERE col1 = i.a <br /> AND col2 = i.b; <br /> END;<br /> END LOOP;<br />END;<br />/<br /><br /><br />Thhis approach is classical and not good for million of records. How it will do with BULK COLLECT & FOR ALL ?<br /><br />Regards,<br />TouqeerAnonymoushttps://www.blogger.com/profile/16657742367810601833noreply@blogger.com