Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Exception handling for insert statement

Avatar of cutie_smily
cutie_smily asked on
Oracle Database
8 Comments1 Solution961 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Avatar of Franck Pachot
Franck PachotFlag of Switzerland imageOracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answers