Need a simple program that inserts records into a table and delete some records after the insert is successful. I have to check for exceptions and commit at the right place. I not sure what exceptions I should check for the insert and when to do the commit.
Here is the same proc I wrote. Please suggest a better way of handling exception and deletes after the insert is successful.
create or replace procedure insert_adj_rec (P_btch_id batch.btchid%type)
l_trans_id trans.trans_id%type := 123;
INSERT INTO TABLE1 (
amount * -1 ------make negative to postive
WHERE BTCH_ID = P_BATCH_ID
AND trans_id = l_Trans_id
and amount < 0);
---after insert is success delete negative amounts
delete from table2
where amount < 0;
WHEN DUP_VAL_ON_INDEX THEN ---not sure if this is right
DBMS_OUTPUT.PUT_LINE ('DUPLICATE RECORD - ' || SQLERRM);
Max points because of urgency.