You can do this by using a save point.
CREATE OR REPLACE PROCEDURE SPTest
-- We create a savepoint here.
INSERT INTO emptest(empid, empname, deptno)
VALUES(1, 'ravi', 10);
INSERT INTO test1(id, name, sal)
VALUES(1, 'raju', 4444);
SET empname = 'hari'
WHERE empid = 1;
-- If any exception occurs
WHEN OTHERS THEN
-- We roll back to the savepoint.
ROLLBACK TO sp_sptest;
-- And of course we raise again,
-- since we don't want to hide the error.
-- Not raising here is an error!
The commit will typically be left to the caller. This construct just guarantees that either all of the inserts and the update are done, or none of them is.
On the most outer level, Oracle will do a rollback by itself. In other words, if you do
EXEC sptest(); on the SQL+ command line, it will be expanded automatically to
But if a calling procedure has exception handling, this doesn't happen, since the exception may not get to that outer level. So you may end up with the inserts done, the update throwing an exception which may be caught and handled by the caller, so that the update will have failed but the inserts succeeded.
By doing a rollback in the stored procedure to the beginning of the procedure, you ensure that all of them succeed, or none of them do.