1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
| DECLARE bulk_error EXCEPTION; PRAGMA EXCEPTION_INIT(bulk_error, -24381); TYPE tbl_exc_type IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
l_exc_tbl tbl_exc_type; l_error_msg VARCHAR2(2000); l_iface_tbl cux_om_ordimp_define_pub.tbl_pub_header_iface; BEGIN l_exc_tbl.delete; FORALL idx IN 1 .. l_iface_tbl.count SAVE EXCEPTIONS INSERT INTO cux_om_so_headers_int VALUES (l_iface_tbl(idx).group_id ,l_iface_tbl(idx).source_code ,l_iface_tbl(idx).ordered_date ,l_iface_tbl(idx).project_number ,l_iface_tbl(idx).order_type ,l_iface_tbl(idx).customer_number ,l_iface_tbl(idx).operation_code ,l_iface_tbl(idx).operation_date ,l_iface_tbl(idx).batch_id ,l_iface_tbl(idx).process_code ,l_iface_tbl(idx).process_date ,l_iface_tbl(idx).error_msg ,l_iface_tbl(idx).currency_code ,l_iface_tbl(idx).creation_date ,l_iface_tbl(idx).created_by ,l_iface_tbl(idx).last_updated_by ,l_iface_tbl(idx).last_update_date ,l_iface_tbl(idx).last_update_login ,l_iface_tbl(idx).attribute_category ,l_iface_tbl(idx).attribute1 ,l_iface_tbl(idx).attribute2 ,l_iface_tbl(idx).attribute3 ,l_iface_tbl(idx).attribute4 ,l_iface_tbl(idx).attribute5 ,l_iface_tbl(idx).attribute6 ,l_iface_tbl(idx).attribute7 ,l_iface_tbl(idx).attribute8 ,l_iface_tbl(idx).attribute9 ,l_iface_tbl(idx).attribute10); EXCEPTION WHEN bulk_error THEN FOR i IN 1 .. SQL%bulk_exceptions.count LOOP l_exc_tbl(i) := SQL%BULK_EXCEPTIONS(i).error_index; l_error_msg := SQLERRM(-sql%BULK_EXCEPTIONS(i).error_code); l_iface_tbl(l_exc_tbl(i)).err_msg := substrb(l_error_msg ,1 ,2000); END LOOP; FOR i IN 1 .. l_excp_tbl.count LOOP UPDATE cux_ebs_control cec SET cec.process_code = 'ERROR' ,cec.process_date = SYSDATE ,cec.error_msg = l_iface_tbl(l_exc_tbl(i)).err_msg WHERE cec.ctrl_id = l_iface_tbl(l_exc_tbl(i)).ctrl_id; END LOOP; END;
|