Use the PLVlog package to write information to a log.
Use the PLVtrc package to trace the execution of your PL/SQL programs.
Both of these packages are used by other PL/Vision packages, PLVlog in particular. For example, when you handle an exception with a PLVexc handler like rec_continue, it, in turn, calls PLVlog to log the error.
The PLVlog (PL/Vision LOGging) package offers a powerful, generic logging mechanism for PL/SQL-based applications. The need to log activity arises in a number of different settings, including logging errors, tracing execution, and auditing activity. Rather than build this functionality over and over again, you can use PLVlog to handle many different circumstances.
The central features of PLVlog include:
Writing of log information to one of several different repositories, including a database table, a PL/SQL table, or an operating system file (for PL/SQL Release 2.3 and above).
Simultaneous, optional display of logged information.
Toggle processing of log activity. You can decide to turn off logging at any time without having to change your own application code.
High-level programs to manage the log. You can display contents of the log with a single program call, and transfer the log contents from a PL/SQL table to a database table.
Automated rollback and savepoint activity. The package provides the ability to preserve database-logged information even if the surrounding transaction is rolled back.
The following sections show how to use each of the different elements of PLVlog.
PLVlog writes information to the log only when it is turned on. The package provides a standard PL/Vision toggle to control the action of the logging mechanism. The programs comprising this toggle are:
PROCEDURE turn_on; PROCEDURE turn_off; FUNCTION logging RETURN BOOLEAN;
One procedure to turn on logging, another to turn it off, and a final function to indicate the current state of affairs (for completeness and politeness). All the two procedures do is set the value of a private Boolean variable, but by correctly applying that Boolean inside an IF statement in PLVlog, the package's user gets to fine-tune the package's behavior.
Why would you turn off logging? You might be executing a production program for a one-time batch of millions of records. If you use the normal logging built into your program, you end up with millions of lines in the log. You can't afford the performance or disk overhead, and you don't really care about the log information for this run. So instead of modifying your program (supposed it is called analyze_sales), you simply turn off logging as shown below:
SQL> exec PLVlog.turn_off; SQL> exec analyze_sales;
PROCEDURE put_line (context_in IN VARCHAR2, code_in IN INTEGER, string_in IN VARCHAR2 := NULL, create_by_in IN VARCHAR2 := USER, rb_to_in IN VARCHAR2 := c_default, override_in IN BOOLEAN := FALSE); PROCEDURE put_line (string_in IN VARCHAR2, rb_to_in IN VARCHAR2 := c_default, override_in IN BOOLEAN := FALSE);
The second version of put_line -- the one with only three arguments -- simply calls the first version with null values for the other arguments. It is provided for convenience, when you simply want to log a string of text and not bother with all the other values. The full set of arguments to put_line is explained in the following:
The context from which the log was called, which usually means the program unit in which put_line was called. You must supply a context.
A numeric code to be stored with the text message. This would usually be the error number, but it could be anything. You must supply an integer code.
The text to be stored in the log. It can be up to 2000 bytes in length.
The name of the user or account that created the line in the log. The default is provided by the builtin USER function.
The name of the savepoint to which you want a rollback to occur. The default value is the currently defined default savepoint in PLVlog. This argument is used when you have called do_rollback; see Section 21.1.5, "Rolling Back with PLVlog" for more information on this argument.
If you pass TRUE for this argument, then the string is written to the log even if logging is otherwise turned off.
If you have turned off logging with a call to PLVlog.turn_off, a call to put_line will not add any information to the log (unless you override that state of affairs).
PLVlog.put_line is called from several different PL/Vision packages; I'll use those programs as examples so you can see how and why put_line has so many arguments.
In the PLVcmt package, you can ask that commits be recorded to the log by calling the PLVcmt.log procedure. When logging is turned on, the put_line program is called at commit time, as shown below:
IF logging THEN PLVlog.put_line ('PLVcmt', 0, v_message || ' ' || context_in, PLVlog.c_noaction, TRUE); END IF;
I provide the context, a generic PLVcmt, an INTEGER code of 0 to indicate success, and the message constructed from various components. Since I am committing, I do not want any rollback activity to occur, so I request "no action" for the savepoint. Finally, I want to record this information even if the user has turned off logging, so I pass TRUE to override the current setting.
The PLVexc exception-handling package uses PLVlog to record errors in one's application. In the code shown below, PLVlog.put_line is only called when the user has requested that an error be recorded. So I again override the current log setting to make sure that this information is written to the log. I also pass in the context, error code and error message associated with the exception. Finally, I do not provide a value for the savepoint argument; I simply rely on the default handling. If the user wants a special rollback action, she provides direction with calls to the appropriate PLVlog programs directly.
IF recording_exception (handle_action_in) THEN PLVlog.put_line (context_in, err_code_in, string_in, override_in => TRUE); END IF;
Sometimes I simply want to take advantage of the PLVlog architecture to allow me to write a string out to a log without having to fuss with all those arguments. In the following code fragment, I request that output be directed to a database table and then execute a series of procedures, documenting their completion with calls to PLVlog.put_line:
BEGIN PLVlog.to_dbtab; calculate_gross_sales (1995, sales$); PLVlog.put_line ('gross sales', sales$); calculate_office_expenses (1995, offexp$); PLVlog.put_line ('office expenses', offexp$); calculate_empl_comp(1995, emplcomp$); PLVlog.put_line ('employee compensation', emplcomp$); PLVcmt.perform_commit; END;
You can use all the arguments or only the minimum. Either way, the put_line logging mechanism should come in very handy. You could even use PLVlog as a lazy way to write information to a PL/SQL table!
The PLVlog package was designed to offer maximum flexibility for logging. The usual logging mechanism requires that you write a record to a database table. This is certainly supported by PLVlog, but you can also select a different type of log, as appropriate to your needs. The log types supported by PLVlog are: database table, PL/SQL table, operating system file, and standard output (your monitor). These different types and how to work with them are explained in the following sections. PLVlog does provide a single program, sendto, that you can use to set the log type. The header for sendto is:
where type_in is the type of log and file_in is the name of the operating system file (relevant only if you are setting the log type to PLV.file). The type_in must be one of the repository constants defined in the PLV package: PLV.dbtab, PLV.pstab, PLV.file, or PLV.stdout.
In addition to this generic type-setting procedure, PLVlog offers a procedure that specifically sets the log type. So to set the log type to a database table, you can enter either of these commands:
SQL> exec PLVlog.sendto (PLV.dbtab)
To set the log type to a PL/SQL table, you can enter either of these commands:
SQL> exec PLVlog.sendto (PLV.pstab)
To set the log type to the mylog.txt operating system file, you can enter either of these commands:
SQL> exec PLVlog.sendto (PLV.file, 'mylog.txt')
Finally, to set the log type to standard output, you can enter either of these commands:
SQL> exec PLVlog.sendto (PLV.stdout)
When you are working with database or PL/SQL table logs, there are some other programs in PLVlog that you may find useful, as I describe below.
If you do decide to write the log to a database table, you can either use the default log table or specify a different table. When PLVlog performs an insert to the log, it uses dynamic SQL (PLVdyn), so it can construct the INSERT statement from your inputs. This approach allows you to use PLVlog for different purposes. You can even use PLVlog within the same application and Oracle connection to write to different logs!
PL/Vision provides a default log table, whose structure is shown below:
CREATE TABLE PLV_log (context VARCHAR2(100), code INTEGER, text VARCHAR2(2000), create_ts DATE, create_by VARCHAR2(100));
where context is the context in which the log entry was made. This might be a program name or a section within a program or an action. The code column is a numeric code. This could be an error code or a number in use in the application. The text column contains a (possibly) long line of text. The create_ts and create_by columns provide a user and date/time audit for the creation of the log entry.
PROCEDURE set_dbtab (table_in IN VARCHAR2 := 'PLV_log', context_col_in IN VARCHAR2 := 'context', code_col_in IN VARCHAR2 := 'code', text_col_in IN VARCHAR2 := 'text', create_ts_col_in IN VARCHAR2 := 'create_ts', create_by_col_in IN VARCHAR2 := 'create_by');
As you can see, set_dbtab provides you with the opportunity to override the default table and column names. Here are two examples of applying set_dbtab to different application circumstances:
Change the name of the table used for logging; the names of the columns remain the default.
Change the name of the table and its columns used for logging.
PLVlog.set_dbtab ('pnl_log', 'progname', 'err_code', 'err_text', 'inserted_on', 'inserted_by');
NOTE: If you use a database table for your log, remember that you must also execute a commit in your session to save the log. In addition, you often need to issue rollbacks so that transactions in error are not saved along with the log.
PLVlog lets you avoid the hassles of writing to a database log during execution of your application by writing instead to a PL/SQL table. This data structure is memory-resident and owned by the session, so you don't have to deal with commits and rollbacks. Just run your application and then either display the log or copy the contents of the log to a database table with the ps2db procedure (explained below).
To set the log repository to the PL/SQL table, execute either:
Then, whenever a line is written to the log, it is deposited in the next available row in the PL/SQL table. The log always starts at row 1 and moves sequentially forward.
The PL/SQL table that stores the log information is a table of 2,000-byte strings, defined as follows:
When a line is written to the table it is formatted as shown below:
context_in || c_delim || TO_CHAR (code_in) || c_delim || string_in || c_delim || TO_CHAR (SYSDATE, PLV.datemask) || c_delim || create_by_in || c_delim
where c_delim is defined to be CHR(8) (see the explanation of the put_line procedure for more information about the individual variables in this string of concatenations). This character shows up in Windows as a black box and is a useful delimiter.
When you are using the PL/SQL table for logging, PLVlog provides two other programs with which to manage the table: clear_pstab and pstab_count. If you want to make sure that the PL/SQL table-based log is empty, call the clear_pstab procedure as follows:
If you want to find out the number of rows currently in the log, you can call pstab_count as shown in the following IF statement (and used in the FOR loop of the showlog.sql script above):
IF PLVlog.pstab_count > 0 THEN PLVlog.display; END IF;
You cannot directly access the PL/SQL table log, since the table itself is defined inside the body of the package and is, therefore, private. On the other hand, you can transfer the PL/SQL table to a database table (with the ps2db procedure). You can also get_line to retrieve a specific row from the table, as discussed in the next section.
PROCEDURE get_line (row_in IN INTEGER, context_out OUT VARCHAR2, code_out OUT INTEGER, string_out OUT VARCHAR2, create_by_out OUT VARCHAR2, create_ts_out OUT DATE);
You supply the row number of the PL/SQL table in which you are interested, and get_line returns the different elements of the logged message. This program is used by the ps2db procedure, and you can use it as well to parse the log information from the PL/SQL table. The following script (stored in the showlog.sql file on the companion disk) displays all the rows in the log that were entered today for the context CALC_TOTALS:
DECLARE v_context PLV_log.context%TYPE; v_code PLV_log.code%TYPE; v_text PLV_log.text%TYPE; v_create_by PLV_log.create_by%TYPE; v_create_ts PLV_log.create_ts%TYPE; BEGIN /* pstab_count is explained below. */ FOR row_ind IN 1 .. PLVlog.pstab_count LOOP PLVlog.get_line (row_ind, v_context, v_code, v_text, v_create_by, v_create_ts); IF v_create_ts BETWEEN TRUNC (SYSDATE) AND TRUNC (SYSDATE+1) AND v_context = 'CALC_TOTALS' THEN p.l (v_text); END IF; END LOOP; END; /
NOTE: If you are on the lookout for handy, concise string manipulation routines, you might want to examine the body of get_line. It contains a local procedure called get_colval, which allows me to cleanly separate out the different elements of my delimited string. It makes use of the betwnstr function of the PLV package, which in and of itself is a useful extra tool.
Why would you bother moving the contents of the log from a PL/SQL table to a database table? You might have relied on the PL/SQL table log during the execution of your application so that you didn't have to worry about commit and rollback processing. Now the test session is done and you want to move your log information to "persistent" data. The PL/SQL table goes away when your session ends. If you move the data to a database table, you can examine the contents at your leisure and with the flexibility offered by the SQL language.
One of the problems with writing log information to a database table is that the information is only available once the new rows are committed to the database. This can be a problem because PLVlog is often employed to track errors -- and in many of these situations, the current transaction has failed or otherwise requires a rollback.
Figure 21.1 illustrates the complexity of maintaining transaction integrity while also preserving records written to a database table log. Transaction A raises an exception. A consequence of the exception is a rollback of the entire transaction. PLVlog is then called to put a line in the log. The application then moves on to the next transaction. If transaction B also raises an exception and issues a rollback, the record written to the log is also erased. So it is necessary to issue a savepoint immediately after the write to the database log. The final complication, however, is that when the rollback of transaction B occurs, it must roll back to the post-log savepoint.
This scenario gives rise to a number of interesting challenges for a generic logging package like PLVlog: how can it avoid saving in-error transactions while saving log information? How can the post-log transaction rollback know about the savepoint? If known, how can this dynamic rollback to a variable savepoint be accomplished? And how can Plvlog make this process as transparent as possible to users of the logging mechanism?
PLVlog offers an architecture to answer these questions. It is complicated and sometimes hard to follow, but it does seem to get the job done.
At a high level, I want to perform steps like this:
Rollback-Transaction Insert-Line-in-Log Set-Savepoint-to-Preserve-Insert
Yet when you examine "Rollback-Transaction" more closely, a question arises: Do I want to perform an unqualified rollback to reject all uncommitted changes, or do I want to roll back to a particular savepoint?
PL/Vision could require that the individual developers who use PLVlog simply handle all of these rollback and savepoint issues. But that approach places a high burden on my users and makes it much less likely that PLVlog will be used to its full potential -- a drawback I take kind of personally. So, instead, PLVlog offers several different toggles, settings, and commands you can use to direct PLVlog to take precisely the right actions in the right order and at the right time.
First, let's go over the way PLVlog performs rollbacks and savepoints, then examine how you can control this aspect of the package's behavior.
If your log repository is an operating system file or PL/SQL table, the concept of a rollback simply doesn't apply. You only have to worry about this complication when you are logging directly to a database table -- which is, after all, a pretty common activity. Suppose, then, that you are using a database table log. PLVlog will still not perform any rollbacks or issue any savepoints unless you explicitly turn on this behavior.
PLVlog offers a standard PL/Vision toggle. This triumvirate of programs is:
One procedure to turn on rollback and savepoint activity, another to turn it off (the default), and a final function to indicate the current state of affairs. All the two procedures do is set the value of a private Boolean variable, but by correctly applying that Boolean inside an IF statement in PLVlog, the package's user gets to fine-tune the package's behavior.
The discussion in the following sections assumes that at some point in your session before you tried to log activity to the database, you issued this command in SQL*Plus:
SQL> exec PLVlog.do_rollback
or directly executed this command inside another PL/SQL program like this:
BEGIN PLVlog.do_rollback; transfer_data; END;
Assuming that you have directed PLVlog to help you manage your transaction automatically when information is sent to the database log, let's take a look at how PLVlog handles the complexities.
The best way to explain this process is to show you the relevant part of the body of the put_line program. The section below is executed only when logging is turned on or overridden in that call to put_line. As you can see, the discussion of rollbacks and this section of code is relevant only when the log type is a database table.
1 IF log_type = PLV.dbtab 2 THEN 3 IF rolling_back 4 THEN 5 do_rb (v_message); 6 END IF; 7 8 put_to_db 9 (context_in, code_in, string_in, create_by_in, 10 SYSDATE); 11 12 IF rolling_back 13 THEN 14 PLVrb.set_savepoint (v_savepoint); 15 END IF;
Lines 8 through 10 contain the call to a private module, put_to_db, that performs the actual INSERT into the specified table. This INSERT is sandwiched between two rollback-related activities. Lines 3 and 12 apply the rollback toggle discussed in the previous section; if you have not requested PLVlog.do_rollback, then rolling_back returns FALSE and the code on lines 5 and 14 are not executed.
Suppose, however, that you have executed do_rollback. Then before a line of data is inserted into your log table, put_line executes the do_rb program, which is a local module in put_line that figures out exactly which type of rollback to execute. This topic is covered in Section 126.96.36.199, "Performing different kinds of rollbacks". After the information is inserted into the log, PLVlog calls the PLVrb.set_savepoint to set a savepoint using dynamic PL/SQL. The savepoint used (v_savepoint) is the current PLVlog savepoint and is discussed in the next section.
By setting a savepoint immediately after the insert, PLVlog gives you a way to roll back all changes except for the last write to the log table. This savepoint is either a savepoint declared by PLVlog or a savepoint you have defined for PLVlog to use, as is explained below.
PLVlog provides a set of constants, shown in the following table, that you can use to modify the way rollbacks occur in the put_line procedure.
Do not perform any kind of rollback.
Perform a full rollback (not back to a savepoint).
Roll back to the default PLVlog savepoint.
Roll back to the last savepoint maintained by the PLVrb package.
Roll back to the PLVlog_savepoint, the initial default savepoint for PLVlog.
You have probably noticed that the values for the first three "savepoints" are not valid savepoint names. Only the constant c_PLVlogsp can actually be used in a SAVEPOINT command. In fact, the other constants are only used by the do_rb program in put_line to determine which kind of ROLLBACK action to take. The do_rb procedure is explored in more detail in Section 188.8.131.52.
The default "rollback to" savepoint of PLVlog is used when the user has turned on rollback activity and the user has not provided an alternative savepoint in the call to put_line. This savepoint is then used to determine the type of rollback to execute before inserting a line into the log.
The default savepoint is initialized to the c_none constant, which means that a full, unqualified rollback is executed.
The procedures rb_to_last and rb_to_default are simply special cases of the rb_to procedure. Let's look at the impact of using these programs with the different savepoint constants and, of course, your own savepoint names.
Set the default savepoint to "no action:"
When you set the "rollback to" savepoint to "no action," no rollback occurs -- even if you have turned on rollbacks with a call to do_rollback. You will usually not pass this constant in to rb_to. Instead, you might do so in a call to put_line so that no rollbacks occur for that single insert.
Set the default savepoint to none:
When you set the "rollback to" savepoint to none, you are requesting a full ROLLBACK, unqualified by any savepoint. This causes all uncommitted changes to be erased from your session.
Set the default savepoint to the default:
By setting the "rollback to" savepoint to the default, you actually set the default savepoint to the constant c_PLVlogsp. You will usually not pass this constant in to rb_to. Instead, you might do so in a call to put_line to ensure that the current default savepoint is used for rollback activity (this, in fact, is the default).
Set the default savepoint to the PLVlog initial value:
By setting the "rollback to" savepoint to this constant, you return the default savepoint back to its initial value. You might do this at the start of a process in order to reset the PLVlog package back to its original values.
Set the default savepoint to the last savepoint issued by PLVrb:
By setting the "rollback to" savepoint to this constant, you are coordinating closely with the PLVrb package, which maintains a stack of savepoints. You are indicating that when a rollback occurs, it should only roll back to the last savepoint issued by PLVrb. You should only use this setting if you are rigorous about using the PLVrb.set_savepoint procedure whenever you issue a SAVEPOINT.
Set the default savepoint to a user-defined savepoint indicating the start of a new order transaction:
By setting the savepoint to this string, you set the default behavior of PLVlog to rollback to this savepoint before an insert to the log table.
You can also use the rollback constants when specifying a value for the rb_to_in argument of put_line. This acts as an override to the default "rollback to" savepoint. Since we've already looked at how put_line works, let's now explore how to use the rb_to_in argument with these constants to change the rollback behavior for a specific call to put_line.
Here, again, is the header for put_line (the abbreviated version):
PROCEDURE put_line (string_in IN VARCHAR2, rb_to_in IN VARCHAR2 := c_default, override_in IN BOOLEAN := FALSE);
The default value for the "rollback to" argument is c_default, which means that PLVlog issues a rollback according to the current default setting (explained in the next section). The following examples show the alternatives to this behavior:
Put a line in the log but do not perform any rollback activity, regardless of the value returned by PLVlog.rolling_back.
PLVlog.put_line (v_err_msg, PLVlog.c_noaction);
Put a line in the log and request a rollback to the last savepoint issued by PLVrb.
PLVlog.put_line (v_err_msg, PLVlog.c_last);
This setting ties in PLVlog as tightly as possible with the use of PLVrb to manage and issue savepoints in your application.
Put a line in the log and request a full, unqualified rollback.
PLVlog.put_line (v_err_msg, PLVlog.c_none);
The constant is called c_none because PLVlog does not roll back to any savepoint, instead it just issues a ROLLBACK command.
Put a line in the log, rolling back all changes made since the last SAVEPOINT TO the standard PLVlog savepoint.
PLVlog.put_line (v_err_msg, PLVlog.c_PLVlogsp);
This is not the same as rolling back to the default savepoint, because you may have changed the default with a call to one of the rb_to programs documented in the next section.
In all of the above examples, the rollback activity described applies only to that single call to PLVlog.put_line. Whenever you call put_line and do not provide a value for the rb_to_in argument, PLVlog relies on the default activity you have previously defined (explained below).
PROCEDURE do_rb (msg_in IN VARCHAR2) IS v_sp PLV.plsql_identifier%TYPE := v_rb_to; BEGIN IF rb_to_in != c_default THEN v_sp := rb_to_in; END IF; IF v_sp = c_noaction THEN NULL; ELSIF v_sp = c_none OR v_sp IS NULL THEN PLVrb.perform_rollback (msg_in); ELSIF v_sp = c_default THEN PLVrb.rollback_to (v_sp_PLVlog, msg_in); ELSIF v_sp = c_last THEN PLVrb.rb_to_last (msg_in); ELSE PLVrb.rollback_to (v_sp, msg_in); END IF; END;
Allow me to translate:
If you have specified (through the default savepoint value stored in v_savepoint) "no action," then don't do anything.
If you set the default to none or if the current default savepoint itself is NULL, perform a full, unqualified rollback with a call to PLVrb.perform_rollback.
If the current "rollback to" savepoint is the default, roll back to the initial default value for PLVlog: PLVlog_savepoint.
If you have requested a rollback to the last savepoint issued by PLVrb, PLVlog calls the corresponding PLVrb.rb_to_last procedure to implement precisely that functionality.
Finally, for any other savepoint values, PLVlog requests that PLVrb roll back uncommitted changes to that savepoint string.
After the insert to the database log has taken place, PLVlog issues a savepoint if PLVlog.rolling_back returns TRUE. The purpose of this savepoint is to preserve the new log record -- even if a rollback comes surging back from activity after the call to the PLVlog.put_line procedure.
The post-insert savepoint of PLVlog (referred to for the rest of this section simply as "the savepoint") is initialized to the constant c_PLVlogsp, the predefined savepoint of the package.
PROCEDURE set_sp (savepoint_in IN VARCHAR2);
Post-Insert Savepoint Set To
NULL; no savepoint is issued after the insert.
NULL; no savepoint is issued after the insert.
PLVrb.lastsp; this function returns the last savepoint issued by PLVrb.
c_PLVlogsp; the initial value and the default value for the PLVlog package.
PLVlog provides a single display procedure to display the contents of the log, whether the log is stored in the database or in a PL/SQL table. If the log is in a database table, PLVlog makes use of the PLVdyn.intab procedure. If the log is in a PL/SQL table, PLVlog uses the PLVtab.display procedure. Here is the header for the display program:
PROCEDURE display (header_in IN VARCHAR2 := 'PL/Vision Log');
The header, which is optional, is used in the PL/SQL table display, but not for the display of the database table.
Here is an example of using the display procedure: set the log repository to the PL/SQL table, execute the application, and then display the log.
SQL> execute PLVlog.to_pstab SQL> start myapp SQL> execute PLVlog.display Contents of PL/Vision Log proc1 ORA-01476: divisor is equal to zero 12/19/1995 11:41:30 proc2 ORA-06502: PL/SQL: num or val error 12/19/1995 11:41:30
You can also view the contents of the log with SQL if the log is a database table. In the following scenario, I set the log repository to the database table, execute the application, and then display the log.
SQL> execute PLVlog.to_dbtab SQL> start myapp SQL> start inlog CONTEXT CODE TEXT CREATE_TS ------- ----- ------------------------------ ----------------- proc1 -1476 ORA-01476: divisor is equal to 12/19/95 114301 zero proc2 -6502 ORA-06502: PL/SQL: numeric or 12/19/95 114301 value error
where the inlog.sql script is as follows:
SET ARRAYSIZE 10 column code format 999999 column context format a12 column create_ts format a17 column text format a30 SELECT context, code, text, TO_CHAR (create_ts, 'mm/dd/yy hhmiss') create_ts FROM PLV_log ORDER BY create_ts;
Notice that this script uses the default logging table, PLV_log. You can, as noted above, override this default with your own table and column names.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.