Rollback when rasise application error?

Do we need to explicitly call rollback when we call RAISE_APPLICATION_ERROR? The answer is: Maybe.

Test case:

CREATE TABLE A (COL1 VARCHAR2(10));

INSERT INTO A VALUES ('1');

begin
    insert into a values('2');
    RAISE_APPLICATION_ERROR(-20005,'ERROR: ');
end;
/

begin
*
ERROR at line 1:
ORA-20005: ERROR:
ORA-06512: at line 4

SQL> select * from a;

COL1
----------
1
 

If we try rollback manually:

begin
    insert into a values('2');
    rollback;
    RAISE_APPLICATION_ERROR(-20005,'ERROR: ');
end;
/
 

begin
*
ERROR at line 1:
ORA-20005: ERROR:
ORA-06512: at line 4
 

SQL> select * from a;
no rows selected
 

Conclusion: RAISE_APPLICATION_ERROR implicits an rollback for the PL/SQL block ONLY. If you want to rollback the whole transaction, you still need to issue rollback explicitly.