Advanced Oracle Pl/Sql: Programming With Packages (Nutshell Handbook)
ISBN: B00006AVR6
EAN: N/A
EAN: N/A
Year: 1995
Pages: 195
Pages: 195
Authors: Steven Feuerstein, Debby Russell
| Chapter 20 PLVcmt and PLVrb: Commit and Rollback Processing |
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.
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.
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.
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:
Set the savepoint with a call to PLVdyn.plsql. The set_savepoint programs constructs the SAVEPOINT command and executes it dynamically.
Sets the last savepoint value to the provided savepoint.
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);
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.
If you want to use PLVrb, you should also use PLVcmt. This commit package maintains the savepoint stack of PLVrb. If you use PLVrb to set savepoints and then issue explicit COMMITS in your programs, the savepoint stack will be out of sync with your transaction.
| 20.1 PLVcmt: Enhancing Commit Processing | 21. PLVlog and PLVtrc: Logging and Tracing |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.