Transactions and Savepoints
Another action which is performed by BMC_DEBUG.BEGINCALL is creating a savepoint at the start of every instrumented procedure or function. There is a very specific reason for doing this, which relates to being able to handle exceptions with confidence. Consider this uninstrumented example :-
SQL> CREATE TABLE sptest_tab (order_id NUMBER(10) NOT NULL, part_id NUMBER(10) NOT NULL,
CONSTRAINT sptest_tab_pk PRIMARY KEY (order_id,part_id));
Table created.
SQL> CREATE OR REPLACE PACKAGE sptest IS
PROCEDURE proca (in_order_id IN NUMBER);
PROCEDURE procb (in_order_id IN NUMBER);
END sptest;
/
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY sptest IS
PROCEDURE proca (in_order_id IN NUMBER) IS
cnt NUMBER;
BEGIN
procb(in_order_id);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
-- We know what to do
NULL; -- Code here to workaround the exception
END proca;
PROCEDURE procb (in_order_id IN NUMBER) IS
BEGIN
INSERT INTO sptest_tab VALUES (in_order_id,1);
INSERT INTO sptest_tab VALUES (in_order_id,2);
INSERT INTO sptest_tab VALUES (in_order_id,3);
INSERT INTO sptest_tab VALUES (in_order_id,3); -- Primary key violation
INSERT INTO sptest_tab VALUES (in_order_id,4);
INSERT INTO sptest_tab VALUES (in_order_id,5);
END procb;
END sptest;
/
Package body created.
SQL> exec sptest.proca(123);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM sptest_tab;
ORDER_ID PART_ID
----------------- -----------------
123 1
123 2
123 3
3 rows selected.
So here PROCA is calling PROCB, which inserts some rows into the test table, but it also raises an exception due to trying to insert duplicate values into a primary key. As PROCA knows how to handle that exception, it can catch it and take the appropriate action, but that isn't the problem.
The output from the SELECT statement shows that only 3 rows are present in the test table. As PROCB raised an exception in the middle of its processing, only some of its changes were performed before it failed.
This illustrates a key point - If you call a procedure and it raises an unhandled exception, you have no idea what that procedure has done, because DML changes it made before the error will still be in effect in the current transaction.
The problem is, you don't know exactly which actions it has performed, so you cannot choose to catch the exception and continue, as you have no guarantee that what the procedure did was consistent if it did not complete.
Ideally, the procedures would be written like this :-
BEGIN
SAVEPOINT sp_this_proc_name;
-- Your code (including DML) goes here
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO SAVEPOINT sp_this_proc_name;
RAISE;
END;
So if the procedure fails with an unhandled exception, any changes it had made would be rolled back, but the entire transaction would not be, so the caller would have the ability to continue with the transaction if it knew how to handle the error. This is what BEGINCALL and ERROR_HANDLE do for you. BEGINCALL creates a uniquely named savepoint at the start of each procedure, and ERROR_HANDLE rolls back to that savepoint when it is called to process an unhandled exception.
This means you can have confidence when handling unhandled exceptions raised by instrumented procedures you have called. If a procedure does not raise an exception and completes successfully, it has done everything you asked it to do. If it raises an exception, it means it has done *NOTHING*, and any changes it had made up to the point of the failing statement have been rolled back, but the state of the current transaction is unaffected, as it is in the same state as before the failed call.
This is especially important if you are writing an API in PL/SQL, and providing procedure and function calls which perform database operations for an application to call. Those API calls should not COMMIT or ROLLBACK the transaction, as exactly when that happens should be decided by the caller. By creating and using savepoints, BMC_DEBUG allows the caller to have confidence in handling exceptions raised by any of its PL/SQL calls.