1

I am having a problem in Oracle rollback.

In my stored procedure I have multiple insert and update statements. If every thing is fine I am sending success message and also I am tracing success status into one Z_AUDIT_HISTORY table.

Now same thing I need in fail status also first need to rollback all statements then track fail status into this Z_AUDIT_HISTORY table.

My procedure:

create or replace PROCEDURE SPTest
AS 
BEGIN 
  SAVEPOINT sp_sptest;

  insert into emptest(empid,empname,deptno)
  (1,'ravi',10);

  insert into test1(id,name,sal)
  (1,'raju',4444);

  update emptest set empname='hari' where empid=1;

  INSERT INTO Z_AUDIT_HISTORY(AUDIT_HISTORTY_ID,STATUS)
  VALUES(SEQ_AUDIT_HISTORTY_ID.NEXTVAL,'SUCESS');

  DBMS_OUTPUT.PUT_LINE('Sucess');
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO Z_AUDIT_HISTORY(AUDIT_HISTORTY_ID,ASSET_ID,STATUS)
    VALUES(SEQ_AUDIT_HISTORTY_ID.NEXTVAL,p_asset_id,'FAIL');

    DBMS_OUTPUT.PUT_LINE('Whoops');

    ROLLBACK TO sp_sptest;

    RAISE; 
END;

When I execute my procedure I am getting below error.

ORA-06512: at line 1
01438. 00000 -  "value larger than specified precision allowed for this column"
*Cause:    When inserting or updating records, a numeric value was entered
           that exceeded the precision defined for the column.
*Action:   Enter a value that complies with the numeric column's precision,
           or use the MODIFY option with the ALTER TABLE command to expand
           the precision.

1 Answer 1

2

There are three things going on here.

  1. When handling the exception, you do an insert followed by a rollback. That means that insert is rolled back as well. At the very least, switch the order of the statements:

    DBMS_OUTPUT.PUT_LINE('Whoops');
    
    ROLLBACK TO sp_sptest;
    
    INSERT INTO Z_AUDIT_HISTORY(AUDIT_HISTORTY_ID,ASSET_ID,STATUS)
    VALUES(SEQ_AUDIT_HISTORTY_ID.NEXTVAL,p_asset_id,'FAIL');
    
    RAISE;
    
  2. Better yet, handle logging in an autonomous transaction. That means that it's a separate transaction that is not affected by anything happening in the main transaction. For logging, you want this.

    CREATE OR REPLACE PROCEDURE log_audit(
       p_asset_id NUMBER,
       p_status VARCHAR
    ) AS
       PRAGMA AUTONOMOUS TRANSACTION
    BEGIN
       INSERT INTO z_audit_history(audit_history_id, asset_id, status)
       VALUES(seq_audit_history_id.NEXTVAL, p_asset_id, p_status);
    END;
    

    And call it

    DBMS_OUTPUT.PUT_LINE('Whoops');
    
    ROLLBACK TO sp_sptest;
    
    log_audit(p_asset_id, 'FAIL');
    
    RAISE;
    
  3. You get an error because you raise the exception after doing the rollback.

    This is a good thing!

    You do not want the exception to disappear silently, you want to know about it.

    What you can do, is handle the exceptions you're expecting. For instance, if you know an ORA-06512 may occur and you know what you need to do in that case, you may add a specific handler for that situation, either in your stored procedure, or in the code that calls it.

1
  • Thank U very much ,for this , so what can i do , if i want to know the exception. so what type of handler i need.
    – Pydi Raju
    Dec 12, 2014 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.