Exceptions Do Not Rollback Uncommitted Changes

View All Scripts Login to Run Script

Script Name Exceptions Do Not Rollback Uncommitted Changes
Description Any non-query DML statements that complete successfully in your session are not rolled back when an exception occurs - either directly in PL/SQL or propagated out from the SQL engine. You still have the option of either committing or rolling back yourself. If, however, the exception goes unhandled out to the host environment, a rollback almost always occurs (this is performed by the host environment).
Area PL/SQL General
Contributor Steven Feuerstein
Created Thursday March 17, 2016

Statement 1

CREATE TABLE employees
AS
   SELECT * FROM hr.employees

Table created.

Statement 2

My first DML statement changes 107 rows. The second DML statement fails, but you will see in the COUNT after the failure that the rows are still changed.

Impact of First DML Sticks Around

DECLARE
   l_count   PLS_INTEGER;
BEGIN
   SELECT COUNT (*)
     INTO l_count
     FROM employees
    WHERE salary = 10000;

   DBMS_OUTPUT.put_line ('Count=' || l_count);

   /* No problem here. */
   UPDATE employees
      SET salary = 10000;

   BEGIN
      UPDATE employees
         SET last_name = RPAD (last_name, 10000, '*');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLERRM);
   END;

   SELECT COUNT (*)
     INTO l_count
     FROM employees
    WHERE salary = 10000;

   DBMS_OUTPUT.put_line ('Count=' || l_count);
END;

Count=4
ORA-12899: value too large for column "SQL_KATKKKGUONSSHTYWBKZNOREUP"."EMPLOYEES"."LAST_NAME" (actual: 10000, maximum: 25)
Count=107

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database