Transaction Controls and Exception Handlings

When developing a database application using PL/SQL, errors are common. There are two main types of errors: syntax errors and runtime errors. Syntax errors are easily detected by Oracle. If the code contains mistakes, Oracle raises an error, and the program will not compile.

Runtime errors, on the other hand, occur during program execution. Unlike syntax errors, they are not caught at compile time but arise when the program is running, potentially causing it to crash or produce incorrect results. Thus, it is the developer's responsibility to anticipate what kind of errors might occur and how to handle them. 

PL/SQL developers declare possible runtime errors as exceptions and use an exception-handling block to manage them. Although the exception-handling sections are optional for a PL/SQL block to be compiled, most of the time, this section is essential for a PL/SQL program to run successfully. It enables graceful management of runtime errors, prevents crashes, and ensures the stability of the program.

System Exceptions

The PL/SQL exceptions can be categorized into two main types: system exceptions and user-defined exceptions. System exceptions are predefined by Oracle and automatically raised when a standard runtime error occurs, such as NO_DATA_FOUND or ZERO_DIVIDE. These exceptions are built into the PL/SQL environment and cover most common errors related to database operations and execution logic. So, you can simply reference them in the exception-handling section and specify what to do when it happens. For example:

User-Defined Exceptions

On the other hand, user-defined exceptions are custom exceptions that developers explicitly define to handle specific situations unique to their application logic. These exceptions can be raised using the RAISE statement when certain conditions are met, allowing developers to manage errors that are not covered by system exceptions. By distinguishing between system and user-defined exceptions, developers gain more control over error handling and can tailor responses to both anticipated and unforeseen issues.

The exception section begins with the EXCEPTION clause. For example:

DECLARE
    v_cust_name VARCHAR2(50) := 'Andrew Lee';
    v_cust_cnt  NUMBER := 0;
    e_invalid_cust_name EXCEPTION;
BEGIN
    SELECT COUNT (*) 
    INTO v_cust_cnt 
    FROM customers 
    WHERE full_name = v_cust_name;

    IF v_cust_cnt = 0 THEN
        RAISE e_invalid_cust_name;
    END IF;

EXCEPTION WHEN e_invalid_cust_name THEN
    dbms_output.put_line('No such customer!');
END;
/

The code block shown above checks if a customer exists in the table named customers. In the declaration section, an exception named e_invalid_cust_name is defined. Then, in the executable section, an IF statement checks if the value in v_cust_cnt equals zero. If true, RAISE e_invalid_cust_name raises the previously declared exception. Lastly, the exception section specifies how to handle the raised exception by printing 'No such customer!'.

Transaction Controls 

In a database system, a transaction is a group of DML statements treated as a single unit of work. Transactions ensure data integrity and consistency by either committing all changes made within the transaction or rolling them back if an error occurs. This "all-or-nothing" approach guarantees that the database remains in a consistent state, even in the event of a failure. Particularly, when an exception is raised during a transaction, it is important to roll back all data changes to prevent partial updates, ensuring that the database does not end up in an inconsistent or erroneous state.

• COMMIT:
• Saves all changes made by the transaction to the database.
• Makes the transaction permanent.
• Usage: COMMIT;
• ROLLBACK:
• Undoes all changes made by the transaction since the last COMMIT or ROLLBACK.
• Returns the database to its state before the transaction started.
• Usage: ROLLBACK;
• SAVEPOINT:
• Creates a temporary savepoint within a transaction.
• Allows you to roll back to a specific point within the transaction without affecting the entire transaction.
• Usage: SAVEPOINT my_savepoint;
• ROLLBACK TO SAVEPOINT:
• Rolls back the transaction to the specified savepoint.
• All changes made after the savepoint are undone.
• Usage: ROLLBACK TO SAVEPOINT my_savepoint;

Together with PL/SQL's robust exception handling mechanism, you can use TCL statements to manage transactions in a more controlled manner. For example:

In an RDBMS, a transaction is a group of tasks that is treated as a single, indivisible operation. It is represented as a series of SQL statements that needs to be executed together as a single, logical unit. If a transaction encounters a failure midway, it has to undo all of the transaction. Thus, SQL statements that are grouped within a single transaction either all succeed, a state referred to as being "committed," or they all fail, resulting in a complete undoing of the transaction, known as being "rolled back."

The concept of transaction is particularly important in settings like banking. In a typical bank transaction, a single transaction entails the transfer and deposit of the same amount of money between two accounts, resulting in the creation of two records. Now, consider a bank without robust power supplies. As the computer starts writing the two records for a transaction, a sudden power failure occurs after one record have been processed. With no backup generators in place, the computer abruptly shuts down. When the power in restored and Oracle SQL resumes operation, it identifies the occurrence of an error in the midst of a transaction. 

At this point, the first record has been saved but not committed while the second record remain undone. Since the two records are in a single transaction, Oracle initiates a rollback, that is undoing the uncommitted first record; no transfer and deposit record is stored and the database returns to a consistent state before the interruption.

The key properties of a transaction, captured by the acronym ACID: atomicity, consistency, isolation, and durability. Atomicity means that a transaction is treated as a single unit of work, exemplified by the necessity for all records in a transaction to be either committed or rolled back as a whole - no partial execution is permissible. Consistency mandates that a transaction leaves the database in a coherent state, which is essential for maintaining data integrity. The isolation aspect ensures that transactions concurrently affecting the same record are kept separate through locks, preventing interference and ensuring sequential execution. Lastly, the durability guarantees the permanence of the effects of a completed transaction, surviving system failures. Even if power fails after completion, the database recognizes the transaction's conclusion and verifies the permanence of changes.

To start a transaction, you can use the following syntax:

START TRANSACTION;
-- Your DML Statements
COMMIT;

In the code snippet above, a new transaction is initiated by the command START TRANSACTION. Then the ongoing transaction is concluded by the COMMIT, making the DML statements permanent. Oracle's Transaction Control Language (TCL) provides a suite of commands designed to manage transactions efficiently:

• COMMIT: Permanently saves all changes made within a transaction to the database.
• Makes the changes visible to other users and applications.
• Marks the end of the transaction.
• ROLLBACK: Undoes all changes made within a transaction and reverts the database to its previous state before the transaction began.
• Used in case of errors, failures, or if you want to cancel the transaction.
• SAVEPOINT: Creates a temporary marker within a transaction.
• Allows you to roll back to a specific point within the transaction if needed.
• Useful for complex transactions where you might want to undo only a portion of the changes.

It is worth noting that any DDL statements, such as CREATE TABLE, CREATE VIEW, or ALTER TABLE, will implicitly commit current transaction before and after execution. This means even if other statements within the transaction fail, the DDL changes will still be permanent. Be mindful of this behavior when designing complex database actions.

COMMIT

The COMMIT statement finalizes and permanently saves changes made by DML statements within a transaction. After COMMIT, changes are irreversible; That isn't to say that the data cannot be changed back with additional DML statements; of course it can. But undoing changes with a ROLLBACK is no longer available.

There are two different types of commit events:

• Explicit commit: This occurs when the COMMIT statement is explicitly executed.
• Implicit commit: This happens automatically when a DDL statement is executed or specific events occurs.

COMMIT has a very simple syntax:

COMMIT;

COMMIT and Multiple Sessions

Basically, DML changes made by a user in a session, using INSERT, UPDATE, and/or DELETE, are visible only within that session until a COMMIT is executed. This applies even if the user logs in multiple times. Uncommitted changes remain hidden from other sessions until commit - either explicit or implicit - occurs.

For example, let's say that one user owns a table and granted an UPDATE privilege to the second user. Any changes made by the second user are not visible to others until a commit event. This includes the table owner. However, once the second user commits the changes, they become visible to all sessions.

ROLLBACK

The ROLLBACK statement works just as an "undo" command. It reverses changes made to the database within a specific session, excluding any changes already committed. The basic syntax of a ROLLBACK statement is as follows:

ROLLBACK;

To illustrate, consider a scenario where one INSERT and one DELETE statement are issued. Afterward, a ROLLBACK statement is executed, negating both changes. The tables on which DML statements executed revert to their pre-transaction state, as if the DML statements never occurred. Since these changes were not committed, no other session witnessed them. Uncommitted DML statement changes remain visible only within the issuing session until a rollback.

Note: In cases of abnormal program termination, an implicit rollback occurs, ensuring uncommitted changes at the time of termination, such as in SQL*Plus or SQL Developer, are not committed to the database.

SAVEPOINT

The SAVEPOINT enables the creation of transaction points, letting subsequent COMMIT or ROLLBACK actions selectively save or undo changes to those points. Unlike the all-or-nothing approach, SAVEPOINT provides a finer control over transactions. For example, let's consider a scenario with periodic SAVEPOINTs as follows:

COMMIT;
UPDATE table SET column1 = value1;
SAVEPOINT SP_1;
UPDATE table SET column2 = value2;
ROLLBACK TO SP_1;
COMMIT;

This allows for targeted rollbacks and offers flexibility in transaction control. To be more specific, the ROLLBACK TO SP_1 will undo the UPDATE statement executed on the column2 of the table. Then it ends the transaction by COMMIT.

Key rules include giving SAVEPOINTs names, avoiding duplicates, and noting that are erased after a commit event, resulting in errors if referenced later. SAVEPOINT is particularly useful for managing complex transactions, such as financial reconciliations, allowing incremental validation and targeted rollbacks before final COMMIT.

In this example, 

Best Practices for PL/SQL Exception Handling

Use OTHERS for System Exceptions

While you can technically handle predefined exceptions using specific system exceptions, there are too many to memorize effectively. Instead, it is better to use OTHERS in combination with SQLCODE and SQLERRM. This approach allows you to catch all system exceptions and provide useful information for future debugging.

The SQLCODE function returns the error code of which 

SQLERRM returns the error message associated with the raised exception.

In addition to these two functions, you can also take advantage of Oracle supplied package dbms_utility. Particularly, a subprogram in the package dbms_utility.format_error_backtrace provides at which code line the exception is raised. For example:

Modularize Exception Handling Routines

The SQLCODE and SQLERRM provides detailed information about raised exceptions. These pieces of information are worth tracking for later use. So, it is best practice to create a separate database table to store the information whenever an exception is raised. This approach is particularly beneficial in a team environment. For example, if your team maintains such a table, any exceptions can be monitored and managed together. A proper table