Transaction Control (COMMIT, ROLLBACK, SAVEPOINT)

Transaction control statements are vital for ensuring data integrity and consistency in your Oracle database applications. They allow you to group DML operations into logical units of work. If all operations within a unit succeed, you COMMIT them, making the changes permanent. If any operation fails or you decide to revert changes, you ROLLBACK them, undoing all operations since the last COMMIT or ROLLBACK. SAVEPOINT offers finer-grained control within a transaction.

Note: Understanding transaction control is paramount for building reliable PL/SQL applications. Without proper use of COMMIT, ROLLBACK, and SAVEPOINT, your data can become inconsistent, leading to errors and loss of data integrity. Always consider the transactional boundaries of your Oracle PL/SQL code.

Example 1: Basic COMMIT and ROLLBACK

DECLARE
    v_employee_id NUMBER := 208;
    v_first_name  VARCHAR2(50) := 'Alice';
    v_last_name   VARCHAR2(50) := 'Smith';
    v_email       VARCHAR2(100) := 'ASMITH@example.com';
BEGIN
    -- Start of a transaction
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id, salary)
    VALUES (v_employee_id, v_first_name, v_last_name, v_email, SYSDATE, 'SA_REP', 80, 5000);

    DBMS_OUTPUT.PUT_LINE('Employee ' || v_first_name || ' ' || v_last_name || ' inserted.');

    -- If everything is successful, commit the transaction
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Transaction committed.');

    -- Now, attempt another operation that will be rolled back
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id, salary)
    VALUES (v_employee_id, 'Duplicate', 'Entry', 'DUPLICATE@example.com', SYSDATE, 'SA_REP', 80, 5000); -- This will cause DUP_VAL_ON_INDEX

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Caught DUP_VAL_ON_INDEX. Rolling back the last INSERT.');
        ROLLBACK; -- Rollback the second insert because of the error
        DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
        ROLLBACK; -- Rollback all pending changes
END;
/

-- Verify if the first insert committed and the second one did not
-- SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = 208;

Explanation This example illustrates the fundamental use of COMMIT and ROLLBACK. We first insert a new employee. If this operation is successful, we COMMIT it, making the change permanent. Then, we attempt to insert the same employee ID again, which will raise a DUP_VAL_ON_INDEX exception. In the exception handler, we issue a ROLLBACK. Crucially, this ROLLBACK only undoes the second (failed) INSERT statement, as the first one was already committed. This demonstrates how transactions ensure data integrity in Oracle PL/SQL by allowing units of work to be either fully applied or fully undone.

Example 2: ROLLBACK to SAVEPOINT

DECLARE
    v_employee_id_1 NUMBER := 209;
    v_employee_id_2 NUMBER := 210;
BEGIN
    -- Insert the first employee
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id, salary)
    VALUES (v_employee_id_1, 'Bob', 'Johnson', 'BJOHNSON@example.com', SYSDATE, 'IT_PROG', 60, 6000);
    DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id_1 || ' inserted.');

    SAVEPOINT before_second_insert; -- Set a savepoint

    -- Insert the second employee
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id, salary)
    VALUES (v_employee_id_2, 'Carol', 'Davis', 'CDAVIS@example.com', SYSDATE, 'IT_PROG', 60, 6500);
    DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id_2 || ' inserted.');

    -- Simulate an error condition
    IF v_employee_id_2 = 210 THEN -- Just for demonstration, normally based on some logic
        RAISE_APPLICATION_ERROR(-20001, 'Simulated error before commit.');
    END IF;

    COMMIT; -- This line will not be reached due to the simulated error
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Caught an error: ' || SQLERRM);
        ROLLBACK TO before_second_insert; -- Rollback only to the savepoint
        DBMS_OUTPUT.PUT_LINE('Rolled back to savepoint "before_second_insert".');
        -- Now, optionally, commit the first insert if desired, or re-raise
        COMMIT; -- Committing the first insert only
END;
/

-- Verify that employee 209 is committed, but 210 is not
-- SELECT employee_id, first_name, last_name FROM employees WHERE employee_id IN (209, 210);

Explanation SAVEPOINT allows you to set a marker within a transaction. In this example, we insert employee_id_1, then set a SAVEPOINT. After inserting employee_id_2, we simulate an error. When the error occurs, ROLLBACK TO before_second_insert is executed. This statement only undoes the operations after the before_second_insert savepoint, meaning employee_id_1 remains inserted, while employee_id_2 is rolled back. This provides granular control over long-running transactions in Oracle PL/SQL, allowing partial rollbacks without discarding all prior successful operations.

Example 3: Explicit Transaction Control with Procedures

-- Procedure to insert an employee (without commit)
CREATE OR REPLACE PROCEDURE insert_employee_proc (
    p_employee_id NUMBER,
    p_first_name  VARCHAR2,
    p_last_name   VARCHAR2
) IS
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id, salary)
    VALUES (p_employee_id, p_first_name, p_last_name, p_first_name || '.' || p_last_name || '@example.com', SYSDATE, 'AC_ACCOUNT', 90, 4000);
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_employee_id || ' inserted by procedure.');
END;
/

-- Main anonymous block to control the transaction
DECLARE
    v_emp_id_A NUMBER := 211;
    v_emp_id_B NUMBER := 212;
BEGIN
    -- Call the procedure multiple times
    insert_employee_proc(v_emp_id_A, 'David', 'Brown');
    insert_employee_proc(v_emp_id_B, 'Eve', 'Green');

    -- If all calls succeed, commit the entire transaction
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('All employee insertions committed.');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        ROLLBACK; -- Rollback all changes made by the procedure calls
        DBMS_OUTPUT.PUT_LINE('All pending changes rolled back.');
END;
/

Explanation In this example, we define a procedure insert_employee_proc that performs an INSERT but does not include a COMMIT. This is a common and recommended practice: procedures should typically not commit or rollback themselves unless they represent a complete, atomic business transaction. Instead, the calling block (the anonymous PL/SQL block here) is responsible for managing the transaction. If both insert_employee_proc calls succeed, the main block COMMITs the changes. If any error occurs within the main block or any called procedure, the ROLLBACK in the exception handler will undo all insertions, ensuring transactional integrity across multiple operations in Oracle PL/SQL.

Example 4: Implied COMMIT/ROLLBACK at Session End

-- This example demonstrates what happens implicitly
-- It's not a code block to run, but a concept explanation.

-- Scenario 1: Successful PL/SQL Block Execution
-- If a PL/SQL block completes successfully without an explicit COMMIT or ROLLBACK,
-- the pending changes will be automatically committed by Oracle.
/*
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id, salary)
    VALUES (213, 'Frank', 'White', 'FWHITE@example.com', SYSDATE, 'PU_CLERK', 50, 3000);
    DBMS_OUTPUT.PUT_LINE('Employee 213 inserted.');
    -- No COMMIT here
END;
/
-- After execution, if no error, Employee 213 would be committed.
*/

-- Scenario 2: Unhandled Exception or Session Termination
-- If an unhandled exception occurs in a PL/SQL block,
-- or if the database session terminates abnormally,
-- all pending changes will be automatically rolled back by Oracle.
/*
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id, salary)
    VALUES (214, 'Grace', 'Black', 'GBLACK@example.com', SYSDATE, 'PU_CLERK', 50, 3000);
    DBMS_OUTPUT.PUT_LINE('Employee 214 inserted.');

    -- Simulate an unhandled exception
    RAISE NO_DATA_FOUND; -- This will cause an unhandled exception

    -- This line will not be reached
    COMMIT;
END;
/
-- After execution, Employee 214 would be rolled back due to the unhandled exception.
*/

Explanation Oracle automatically manages transactions at the session level. If a PL/SQL block or a SQL statement completes successfully without an explicit COMMIT or ROLLBACK, the changes are implicitly COMMITted. Conversely, if an unhandled exception occurs or the database session terminates abnormally (e.g., client disconnection), all pending changes are implicitly ROLLBACKed. While this automatic behavior exists, it's generally best practice to explicitly manage your transactions with COMMIT and ROLLBACK for clarity, control, and robust error handling in Oracle PL/SQL applications.

Example 5: SET TRANSACTION READ ONLY

DECLARE
    v_total_salary NUMBER;
BEGIN
    -- Set the transaction to read-only mode.
    -- No DML operations (INSERT, UPDATE, DELETE) are allowed in this transaction.
    SET TRANSACTION READ ONLY;

    SELECT SUM(salary)
    INTO v_total_salary
    FROM employees
    WHERE department_id = 60;

    DBMS_OUTPUT.PUT_LINE('Total salary for department 60: ' || v_total_salary);

    -- Attempting a DML operation will result in an error: ORA-01456
    -- INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id, salary)
    -- VALUES (215, 'Ian', 'King', 'IKING@example.com', SYSDATE, 'IT_PROG', 60, 5000);

    -- This COMMIT is technically not needed for READ ONLY transactions,
    -- but explicitly ends the transaction and releases read locks.
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Read-only transaction committed (session returned to default mode).');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        -- A ROLLBACK or COMMIT will end the read-only transaction mode.
        ROLLBACK;
END;
/

Explanation The SET TRANSACTION READ ONLY statement is used to define a transaction where no DML operations are allowed. This is particularly useful for reports or queries that need to see a consistent snapshot of the data without the risk of accidentally modifying it. Once set, any INSERT, UPDATE, or DELETE attempt will raise an ORA-01456 error. A COMMIT or ROLLBACK statement (or session end) terminates the read-only transaction and returns the session to its default read-write mode. This ensures data integrity and consistency for read-only analytical operations in Oracle database environments.

Implicit Cursors (SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN)

Whenever you execute a DML statement (INSERT, UPDATE, DELETE) or a SELECT ... INTO statement in PL/SQL, Oracle implicitly opens a cursor to process that SQL statement. You don't explicitly declare or open these cursors; they are managed automatically by the PL/SQL engine. However, PL/SQL provides a set of attributes, known as implicit cursor attributes, that allow you to check the outcome of these implicit SQL operations. These attributes are extremely valuable for flow control, error checking, and providing feedback to users.

Note: Implicit cursor attributes are crucial for understanding the success or failure of your DML and single-row SELECT statements. They provide direct feedback on how many rows were affected, whether a row was found, and the status of the implicit cursor. Mastering these attributes is essential for writing robust and responsive Oracle PL/SQL applications.

SQL%FOUND

SQL%FOUND is a boolean attribute that returns TRUE if an INSERT, UPDATE, or DELETE statement affected at least one row, or if a SELECT ... INTO statement returned one or more rows. It returns FALSE otherwise.

Example 1: SQL%FOUND with UPDATE

DECLARE
    v_employee_id NUMBER := 207; -- Assuming this employee exists from previous examples
    v_new_salary  NUMBER := 8500;
BEGIN
    UPDATE employees
    SET salary = v_new_salary
    WHERE employee_id = v_employee_id;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Salary for employee ' || v_employee_id || ' updated successfully.');
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' not found, no salary update performed.');
        ROLLBACK;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        ROLLBACK;
END;
/

Explanation After the UPDATE statement executes, SQL%FOUND is checked. If the UPDATE statement affected one or more rows (meaning an employee with v_employee_id was found and updated), SQL%FOUND will be TRUE, and a success message is displayed, followed by a COMMIT. If no rows were updated (employee not found), SQL%FOUND will be FALSE, and a "not found" message is displayed, then ROLLBACK is issued. This demonstrates how to use SQL%FOUND to confirm the success of DML operations in Oracle PL/SQL.

Example 2: SQL%FOUND with DELETE

DECLARE
    v_employee_id NUMBER := 999; -- Employee ID that likely does not exist
BEGIN
    DELETE FROM employees
    WHERE employee_id = v_employee_id;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' deleted successfully.');
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' not found, no deletion performed.');
        ROLLBACK;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        ROLLBACK;
END;
/

Explanation Similar to the UPDATE example, this block uses SQL%FOUND after a DELETE statement. If employee_id = 999 exists and is deleted, SQL%FOUND will be TRUE. If it doesn't exist, SQL%FOUND will be FALSE. This provides immediate feedback on whether the deletion operation was successful in terms of affecting any rows, which is crucial for controlling application flow in Oracle PL/SQL.

SQL%NOTFOUND

SQL%NOTFOUND is the opposite of SQL%FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected zero rows, or if a SELECT ... INTO statement returned no rows. It returns FALSE otherwise.

Example 1: SQL%NOTFOUND with SELECT INTO

DECLARE
    v_employee_name VARCHAR2(100);
    v_employee_id   NUMBER := 1; -- Employee ID that likely does not exist in typical HR schema
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_employee_name
    FROM employees
    WHERE employee_id = v_employee_id;

    DBMS_OUTPUT.PUT_LINE('Employee found: ' || v_employee_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- This is where SQL%NOTFOUND for SELECT...INTO would implicitly occur
        -- if we didn't handle NO_DATA_FOUND explicitly.
        -- However, it's generally better to use the exception handler for NO_DATA_FOUND
        -- for SELECT...INTO, as it's a specific error condition.
        DBMS_OUTPUT.PUT_LINE('Employee ID ' || v_employee_id || ' not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

-- Alternative usage of SQL%NOTFOUND for SELECT...INTO (less common for NO_DATA_FOUND, but valid)
DECLARE
    v_employee_name VARCHAR2(100);
    v_employee_id   NUMBER := 1;
BEGIN
    BEGIN
        SELECT first_name || ' ' || last_name
        INTO v_employee_name
        FROM employees
        WHERE employee_id = v_employee_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_employee_name := NULL; -- Assign NULL if not found
    END;

    IF SQL%NOTFOUND THEN -- This will be true if the inner SELECT...INTO resulted in NO_DATA_FOUND
        DBMS_OUTPUT.PUT_LINE('Employee ID ' || v_employee_id || ' not found using SQL%NOTFOUND check.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee found: ' || v_employee_name || ' using SQL%NOTFOUND check.');
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Explanation For SELECT ... INTO, if no rows are returned, a NO_DATA_FOUND exception is raised. While SQL%NOTFOUND will technically be TRUE in this scenario, it's generally preferred to handle NO_DATA_FOUND with an explicit exception handler for clarity and proper error management. The second part of the example shows a less common but valid way to use SQL%NOTFOUND after a SELECT...INTO by suppressing NO_DATA_FOUND with a nested block and then checking the attribute. This illustrates how SQL%NOTFOUND provides insights into the results of single-row queries in Oracle PL/SQL.

Example 2: SQL%NOTFOUND with INSERT (after checking for existence)

DECLARE
    v_new_employee_id NUMBER := 216;
    v_first_name      VARCHAR2(50) := 'Zoe';
    v_last_name       VARCHAR2(50) := 'Miller';
    v_exists_check    NUMBER;
BEGIN
    -- Check if employee already exists before inserting
    BEGIN
        SELECT 1 INTO v_exists_check FROM employees WHERE employee_id = v_new_employee_id;
        DBMS_OUTPUT.PUT_LINE('Employee ID ' || v_new_employee_id || ' already exists. Skipping insert.');
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- Employee does not exist, proceed with insert
            INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id, salary)
            VALUES (v_new_employee_id, v_first_name, v_last_name, v_first_name || '.' || v_last_name || '@example.com', SYSDATE, 'ST_CLERK', 50, 3000);

            IF SQL%NOTFOUND THEN -- This would typically be FALSE after a successful insert
                DBMS_OUTPUT.PUT_LINE('Unexpected: Insert affected no rows.');
            ELSE
                DBMS_OUTPUT.PUT_LINE('Employee ' || v_new_employee_id || ' inserted successfully.');
            END IF;
            COMMIT;
    END;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        ROLLBACK;
END;
/

Explanation This code demonstrates a common pattern: check for existence before inserting to prevent DUP_VAL_ON_INDEX. If the SELECT statement finds no data (meaning NO_DATA_FOUND is raised and handled), then the INSERT proceeds. After a successful INSERT, SQL%NOTFOUND would logically be FALSE because at least one row was affected. This usage of SQL%NOTFOUND confirms whether the INSERT operation successfully added a row, which is important for logic branches in Oracle database programming.

SQL%ROWCOUNT

SQL%ROWCOUNT is a numeric attribute that returns the number of rows affected by the most recent INSERT, UPDATE, or DELETE statement, or the number of rows returned by a SELECT ... INTO statement (which will always be 1 for a successful single-row query).

Example 1: SQL%ROWCOUNT with UPDATE

DECLARE
    v_department_id NUMBER := 60;
    v_increase_pct  NUMBER := 0.05; -- 5% increase
BEGIN
    UPDATE employees
    SET salary = salary * (1 + v_increase_pct)
    WHERE department_id = v_department_id;

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees in department ' || v_department_id || ' had their salaries updated.');

    IF SQL%ROWCOUNT > 0 THEN
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employees found in department ' || v_department_id || ' to update.');
        ROLLBACK;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        ROLLBACK;
END;
/

Explanation After updating salaries for all employees in department_id = 60, SQL%ROWCOUNT provides the exact number of rows that were modified. This information is then used to inform the user and decide whether to COMMIT the transaction (if any rows were updated) or ROLLBACK (if no rows were found to update), providing precise control over bulk operations in Oracle PL/SQL.

Example 2: SQL%ROWCOUNT with DELETE

DECLARE
    v_job_id VARCHAR2(10) := 'IT_PROG';
BEGIN
    DELETE FROM job_history
    WHERE job_id = v_job_id
    AND end_date < ADD_MONTHS(SYSDATE, -12); -- Delete history older than 1 year

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' old job history records for job ' || v_job_id || ' deleted.');

    IF SQL%ROWCOUNT > 0 THEN
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No old job history records found for job ' || v_job_id || ' to delete.');
        ROLLBACK;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        ROLLBACK;
END;
/

Explanation This example demonstrates using SQL%ROWCOUNT after a DELETE operation. It precisely tells us how many rows were removed from the job_history table based on the specified criteria. This is invaluable for reporting on the impact of DML operations and for making conditional decisions, such as whether to COMMIT or ROLLBACK, based on the actual number of affected rows in your Oracle database.

SQL%ISOPEN

SQL%ISOPEN is a boolean attribute that always returns FALSE for implicit cursors. This is because Oracle automatically closes implicit cursors immediately after the SQL statement has been processed. While it exists, it's generally not used for implicit cursors in practical PL/SQL programming because their lifecycle is fully managed by the database. It is primarily relevant for explicit cursors, which you explicitly open and close.

Example 1: SQL%ISOPEN for Implicit Cursor (Always FALSE)

DECLARE
    v_employee_name VARCHAR2(100);
    v_salary        NUMBER;
BEGIN
    SELECT first_name || ' ' || last_name, salary
    INTO v_employee_name, v_salary
    FROM employees
    WHERE employee_id = 100;

    -- SQL%ISOPEN will always be FALSE immediately after an implicit SQL statement.
    IF SQL%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('Implicit cursor is open.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Implicit cursor is closed (as expected).');
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Explanation This code snippet illustrates that SQL%ISOPEN will always evaluate to FALSE immediately after an implicit SQL statement completes. This confirms the automatic management of implicit cursors by Oracle, where they are opened, processed, and then closed instantly. While not directly useful for controlling implicit cursor behavior, understanding this attribute helps differentiate between implicit and explicit cursor handling in Oracle PL/SQL development.