Oracle 11g – How to Perform Rollback in Oracle

oracle-11gplsql

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.

Best Answer

There are three things going on here.

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;

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;

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.

Related Solutions

ORA-01562: failed to extend rollback segment number 11

When you delete data, oracle save delete data because perhaps transaction don't ends with commit and oracle need to restore delete data.

This copy of data is stored in rollback segments. In Oracle settings you have max extends for this segments.

Solution

You can extend segments
You can delete data by 'slices':

Sample spliting delete data:

 DELETE FROM table SC
 WHERE SC.evaluationMonth= 'Some month' 
 and SC.evaluationDay= '1';
 COMMIT;

 DELETE FROM table SC
 WHERE SC.evaluationMonth= 'Some month' 
 and SC.evaluationDay= '2';
 COMMIT;

 ...

 DELETE FROM table SC
 WHERE SC.evaluationMonth= 'Some month' 
 and SC.evaluationDay= '31';
 COMMIT;

(you can do a loop, of course )

How to Run Procedure in Oracle SQL Developer

First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure.

begin
  hotel.fill_city(10000);
end;
/

should correctly invoke your procedure.

Second, you have issues with the naming of your local variables. Normally, you would not create local variables like city and postal_number that are the same as the names of columns in tables in your database. That makes it far too easy to introduce errors in your code where you intend to refer to the local variable but scope resolution rules mean that you are really referring to the column name. For example, if you write the perfectly valid function

CREATE OR REPLACE FUNCTION get_dname( deptno IN NUMBER )
  RETURN VARCHAR2
IS
  dname VARCHAR2(30);
BEGIN
  SELECT dname
    INTO dname
    FROM dept
   WHERE deptno = deptno;
  RETURN dname;
END;

in your WHERE clause, both references to deptno will resolve to the column in the dept table, not to the parameter deptno. That means that no matter what value you pass in to the function, the SELECT statement will return every row from the table and, thus, throw a too_many_rows error. Normally, you would come up with a convention on how to name variables and parameters that would not conflict with your table naming conventions. Prefixing parameters with p_ and local variables with l_ is one common convention. That turns our function into something like this

CREATE OR REPLACE FUNCTION get_dname( p_deptno IN NUMBER )
  RETURN VARCHAR2
IS
  l_dname VARCHAR2(30);
BEGIN
  SELECT dname
    INTO l_dname
    FROM dept
   WHERE deptno = p_deptno;
  RETURN l_dname;
END;

The other option would be to use explicit scoping prefixes when referring to local variables (i.e. get_dname.dname and get_dname.deptno) rather than altering the names of your local variables.

Best Answer

Related Solutions

ORA-01562: failed to extend rollback segment number 11

How to Run Procedure in Oracle SQL Developer

Related Question