20.2.1 Controlling Rollbacks

One of the big advantages to using PLVrb instead of direct calls to ROLLBACK is that you have placed a layer of code between your application and the ROLLBACK. This layer gives you (through PL/Vision) the ability to modify rollback processing behavior without changing your application code. This is very important because it allows you to stabilize your code, but still change the way it works for purposes of testing and debugging.

PLVrb offers a standard PL/Vision toggle to control rollback processing. This triumvirate of programs is:

PROCEDURE turn_on;
PROCEDURE turn_off;
FUNCTION rolling_back RETURN BOOLEAN;

All that the first two procedures do is set the value of a private Boolean variable, but by correctly applying that Boolean inside an IF statement in PLVrb, the package's user gets to fine-tune the package's behavior.

20.2.2 Logging Rollbacks

The rollback action in an application is a critical step. It is, for one thing, irreversible. Once you rollback, you cannot un-rollback. It is often very useful to know when rollbacks have taken place and the action that was taken around that rollback point. I have found this to be most important when I am executing long-running processes. Have I issued any rollbacks? If so, what was the cause? The PLVrb logging facility gives me the answers to these types of questions.

Whenever you call PLVrb.perform_rollback , PLVrb.rb_to_last , and PLVrb.rollback_to , you can supply a string or context for that action. This string is ignored unless logging is turned on. If logging is enabled, PLVrb calls the PLVlog facility to log your message. You can, within PLVlog, send this information to a database table, PL/SQL table, operating system file (with Release 2.3 of PL/SQL ), or standard output (your screen).

PLVrb offers a standard PL/Vision toggle to control the logging of rollback processing. This triumvirate of programs is:

PROCEDURE log;
PROCEDURE nolog;
FUNCTION logging RETURN BOOLEAN;

NOTE: You do not have to turn on logging in PLVlog for the PLVrb log to function properly. It automatically turns on logging in PLVlog in order to write its rollback-related information, and then reset the PLVlog status to its prior state.

20.2.3 Setting Savepoints

When you set a savepoint in PL/SQL , you give yourself a spot in your code to which you can rollback your changes. This is useful when you need to discard some, but not all, of your uncommitted changes. The usual situation you face with PL/SQL is that you must hard code the names of savepoints in your code. A savepoint is, in fact, an undeclared identifier. You don't (and cannot) declare a savepoint in your declaration section, as you would with an exception. Instead, you simply provide an identifier after the keyword SAVEPOINT in your code and that savepoint is established. Then, when you issue a ROLLBACK, you must also hard code that same identifier value in the ROLLBACK.

In the following block of code, I set a savepoint and then in the exception section rollback to that same savepoint.

BEGIN
   SAVEPOINT start_trans;
   INSERT INTO emp ...;
   DELETE FROM emp_history ... ;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK TO start_trans;
END;

I cannot, on the other hand, write code like this:

PACKAGE empsav
IS
   insert_point VARCHAR2(10) := insert ;
   delete_point VARCHAR2(10) := delete ;
END;

BEGIN
   SAVEPOINT empsav.insert_point;
   INSERT INTO emp ... ;
   SAVEPOINT empsav.delete_point;
   DELETE FROM emp_history ... ;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      ROLLBACK TO empsav.insert_point;

   WHEN empsav.still_active
   THEN
      ROLLBACK TO empsav.delete_point;
END;

PL/SQL will not, absolutely not, evaluate the packaged contents into a literal and then use that literal to direct rollback activity. Instead, the code will fail to compile as shown:

SAVEPOINT empsav.insert_point;
                *
ERROR at line 2:
ORA-06550: line 2, column 20:
PLS-00103: Encountered the symbol "." when expecting one of the
   following:

One consequence of this hard-coding is that you must know the name of the savepoint at compile time, not at runtime. In most situations, this might be fine. In other programs, this can be a significant obstacle. PLVlog, for example, offers a generic logging mechanism. When logging to a database table, you must often perform a ROLLBACK before an INSERT to the log table and then follow up with the setting of a SAVEPOINT. And it really needs to do these steps for a dynamically determined savepoint.

This dynamic setting of savepoints (and rolling back to those savepoints) is provided by the PLVrb package. To set a savepoint whose name is determined at runtime, call the set_savepoint procedure. The header for this program is:

   PROCEDURE set_savepoint (sp_in IN VARCHAR2);

where sp_in is the savepoint name. The sp_in argument must be a valid PL/SQL identifier (starts with a letter, is composed of letters, digits, $, #, and the underscore character, and must be no longer than 30 characters in length).

Every time you set a savepoint, the procedure takes the following actions:

1. Set the savepoint with a call to PLVdyn.plsql . The set_savepoint programs constructs the SAVEPOINT command and executes it dynamically.

2. Sets the last savepoint value to the provided savepoint.

3. Pushes the savepoint onto the stack of savepoints maintained by PLVrb using the PLVstk package.

Instead of issuing statements like this:

SAVEPOINT start_trans;

you can now pass the name of your savepoint to PLVrb for setting:

PLVrb.set_savepoint ('start_trans');

or:

PLVrb.set_savepoint (v_starttrans_sp);

20.2.4 Performing Rollbacks

PLVrb offers three programs to perform rollbacks: perform_rollback , rollback_to , and rb_to_last . The headers for these programs are:

PROCEDURE 

perform_rollback
   (context_in IN VARCHAR2 := NULL);

PROCEDURE 

rollback_to
   (sp_in IN VARCHAR2,
    context_in IN VARCHAR2 := NULL);

PROCEDURE 

rb_to_last
   (context_in IN VARCHAR2 := NULL);

In all three procedures, the context_in argument is a string that will be logged using PLVlog if you have called PLVrb.log to turn on logging.

When you call perform_rollback , a full, unqualified rollback is performed; no savepoint is used, and all uncommitted changes are rolled back.

When you call rollback_to , PLVrb issues a ROLLBACK to the specified savepoint. Besides issuing the savepoint, PLVrb also removes from the savepoint stack any savepoints that came after the savepoint you specified. If the savepoint argument is not in the PLVrb savepoint stack, the stack is emptied.

When you call rb_to_lst , PLVrb issues a ROLLBACK to the savepoint specified in the most recent call to set_savepoint .