cutie_smily
asked on
Exception handling for insert statement
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)
IS
l_trans_id trans.trans_id%type := 123;
BEGIN
INSERT INTO TABLE1 (
COL1,
COL2,
COL3)
VALUES (
SELECT COL1,
COL2,
amount * -1 ------make negative to postive
FROM TABLE2
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;
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN ---not sure if this is right
DBMS_OUTPUT.PUT_LINE ('DUPLICATE RECORD - ' || SQLERRM);
ROLLBACK;
END;
Max points because of urgency.
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)
IS
l_trans_id trans.trans_id%type := 123;
BEGIN
INSERT INTO TABLE1 (
COL1,
COL2,
COL3)
VALUES (
SELECT COL1,
COL2,
amount * -1 ------make negative to postive
FROM TABLE2
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;
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN ---not sure if this is right
DBMS_OUTPUT.PUT_LINE ('DUPLICATE RECORD - ' || SQLERRM);
ROLLBACK;
END;
Max points because of urgency.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appreciate all the inputs. Please help me on the below warning messages.
Warning(1): PLW-06015: parameter PLSQL_DEBUG is deprecated; use PLSQL_OPTIMIZE_LEVEL = 1
Warning(1): PLW-06013: deprecated parameter PLSQL_DEBUG forces PLSQL_OPTIMIZE_LEVEL <= 1
Warning(2,1): PLW-05018: unit SP_hpw_ADJ omitted optional AUTHID clause; default value DEFINER used
Warning(100,1): PLW-06009: procedure "SP_hpw_ADJ" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
Warning(1): PLW-06015: parameter PLSQL_DEBUG is deprecated; use PLSQL_OPTIMIZE_LEVEL = 1
Warning(1): PLW-06013: deprecated parameter PLSQL_DEBUG forces PLSQL_OPTIMIZE_LEVEL <= 1
Warning(2,1): PLW-05018: unit SP_hpw_ADJ omitted optional AUTHID clause; default value DEFINER used
Warning(100,1): PLW-06009: procedure "SP_hpw_ADJ" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
Try this and compile the procedure
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEV
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEV
ASKER
Thanks for all the help and apologize for the late reply.
"i dont think u can use directly SQLERRM in the dbms_output"
I tried the below two statements in the exception and both worked so I think we can use the sqlerrm in the dbms_output.
DBMS_OUTPUT.PUT_LINE('SQLE
DBMS_OUTPUT.PUT_LINE('SQLC
"i dont think u can use directly SQLERRM in the dbms_output"
I tried the below two statements in the exception and both worked so I think we can use the sqlerrm in the dbms_output.
DBMS_OUTPUT.PUT_LINE('SQLE
DBMS_OUTPUT.PUT_LINE('SQLC
ASKER
Please suggest a better way of writing this procedure.