The PLVrb PL/Vision RollBack package provides a programmatic interface to rollback activity in PL/SQL . With PLVrb, you no longer issue explicit ROLLBACK and SAVEPOINT commands. Instead, you call the appropriate PLVrb module. This layer of code gives you the ability to change transaction-level behavior at runtime. With PLVrb, you can even execute soft-coded savepoints, a feat usually considered impossible in PL/SQL .
These different elements of the PLVrb package are explained in the following sections.
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.
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.
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;
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:
Instead of issuing statements like this:
you can now pass the name of your savepoint to PLVrb for setting:
PLVrb offers three programs to perform rollbacks: perform_rollback , rollback_to , and rb_to_last . The headers for these programs are:
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.