oracle11g - Continuing the loop from exception block -

oracle11g - Continuing the loop from exception block -

i understand question had been answered , have referred question . when trying compile next code whereby trying go through loop contains insert statement ,if insert statement not successful need check if error thrown dml-insert failure unique constraint or not ,if unique constraint error (-1) need perform update statement , go on loop.

oracle version : 11g

create or replace procedure "testsample" type array_test table of number; test_arr array_test := array_test(1, 2, 3); v_key number; dup_chk number; v_excp_error_code varchar2(25); begin in test_arr.first .. test_arr.last loop dbms_output.put_line(i || ' = ' || test_arr(i)); dup_chk := test_arr(i); insert trig_test values('test324', test_arr(i), 'y', 'y') returning key v_key; if v_key not null dbms_output.put_line(' commiting insert ' || v_key || ' ' || dup_chk); commit; end if; exception when others v_excp_error_code := sqlcode; dbms_output.put_line('error code = ' || v_excp_error_code); if v_excp_error_code = '-1' dbms_output.put_line('entered unique constraint exception ' || v_key); if v_key = dup_chk or v_key null update trig_test set status = 'tstsample' key = v_key; dbms_output.put_line('updated '); commit; end if; else raise; end if; end; end loop; end testsample;

the above stored procedure throwing next compilation error ,

error(33,3): pls-00103: encountered symbol "exception" when expecting 1 of following: ( begin case declare end exit goto if loop mod null pragma raise homecoming select update while << go on close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

error(56,7): pls-00103: encountered symbol "loop" when expecting 1 of following: ;

error(59): pls-00103: encountered symbol "end-of-file" when expecting 1 of following: end not pragma final instantiable order overriding static fellow member constructor map

highly appreciate help

thanks

you're missing begin after loop. ie:

loop begin -- add together dbms_output.put_line(i || ' = ' || test_arr(i)); dup_chk := test_arr(i); insert trig_test values ('test324', test_arr(i), 'y', 'y') returning key v_key; if v_key not null dbms_output.put_line(' commiting insert ' || v_key || ' ' || dup_chk); commit; end if; exception

instead of trapping others though, interested in ora-00001 can trap when dup_val_on_index instead , not check sqlcode.

oracle11g