Please I will be grateful if anyone can help on how to continue processing in a loop if insert into another table fails.
This question was asked in the Forms Forum but I did not get any reply, so I thought I may be able o get help here as this is about PL/SQL block in Oracle Form.
Normally Oracle form will throw an error and stop processing but I want to continue on the next insert while ignoring the error or a logic to log the error but contine processing instead of stalling the form.
I have a sample code here, if record number two for any reason fails to insert to table order_test_import, continue to insert record 3 etc.
In this code I have intentionally forced error in this line of code because c1.ord_com is 2 digits long
on the databse.
.
if c1.ord_com = 12 then
c1.ord_com := 123;
end if;
The idea is to continue inserting the next record after this error.
Many thanks for your help.
Ant.
--PROCEDURE populate_data IS
declare
cursor get_ord is
select ord_com ,
ord_num ,
ord_typ ,
ord_qty ,
ord_val ,
ord_dte
from order_test
order by 1;
Begin
--go_block details;
for c1 in get_ord loop
--:details.ord_num := c1.ord_num;
--:details.ord_typ := c1.ord_typ;
--:details.ord_qty := c1.ord_qty;
-- :details.ord_val := c1.ord_val;
-- :details.ord_dte := c1.ord_dte;*/
if c1.ord_com = 12 then
c1.ord_com := 123;
end if;
begin
insert into order_test_import
(
imp_com ,
imp_num ,
imp_typ ,
imp_qty ,
imp_val ,
imp_dte
)
values
(
c1.ord_com,
c1.ord_num,
c1.ord_typ,
c1.ord_qty,
c1.ord_val,
c1.ord_dte
) ;
commit;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
-- please can you tell me how the loop will continue if the insert fails.
end loop;
--first_record;
END;
-- Create table
create table order_test
(
ord_com NUMBER(2) not null,
ord_num NUMBER(10) not null,
ord_typ VARCHAR2(3),
ord_qty NUMBER(8),
ord_val NUMBER(8),
ord_dte DATE
);
-- Create table
create table order_test_import
(
imp_com NUMBER(2) not null,
imp_num NUMBER(10) not null,
imp_typ VARCHAR2(3),
imp_qty NUMBER(8),
imp_val NUMBER(8),
imp_dte DATE
);
REM INSERTING into ORDER_TEST
SET DEFINE OFF;
Insert into ORDER_TEST (ORD_COM,ORD_NUM,ORD_TYP,ORD_QTY,ORD_VAL,ORD_DTE) values (10,34456,'H',20,10,to_date('01-FEB-22','DD-MON-RR'));
Insert into ORDER_TEST (ORD_COM,ORD_NUM,ORD_TYP,ORD_QTY,ORD_VAL,ORD_DTE) values (12,34457,'T',30,10,to_date('01-FEB-22','DD-MON-RR'));
Insert into ORDER_TEST (ORD_COM,ORD_NUM,ORD_TYP,ORD_QTY,ORD_VAL,ORD_DTE) values (14,34458,'T',30,20,to_date('01-FEB-22','DD-MON-RR'));
Insert into ORDER_TEST (ORD_COM,ORD_NUM,ORD_TYP,ORD_QTY,ORD_VAL,ORD_DTE) values (40,34458,'H',40,5,to_date('01-FEB-22','DD-MON-RR'));