21.1.2.1 Using put_line

1. 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.

2. 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;

3. 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!

21.1.3.1 Defining the database log table

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.

PLVlog provides the set_dbtab procedure to change the name of the table and/or the names of each of the columns in the log table. The header for set_dbtab is:

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:

1. Change the name of the table used for logging; the names of the columns remain the default.

   PLVlog.set_dbtab ('pnl_log');

2. 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');

You can use set_dbtab to change some or all of these names, but remember that the table you select for logging must have at least these five columns with the same datatypes as the default table.

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.

21.1.3.2 Closing the file

If you are writing to an operating system file, you need to close that file before you can see log information written to that repository. To close the PLVlog file, use the fclose procedure:

PROCEDURE fclose;

You do not have to specify the file name; that information has been stored inside the package.

21.1.4.1 Counting and clearing the PL/SQL table

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:

PLVlog.



clear_pstab;

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.

21.1.4.2 Retrieving log data from PL/SQL table

You can unpack a log string in the PL/SQL table into individual variables with the get_line procedure, whose header is shown below:

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.

21.1.4.3 Transferring a PL/SQL table log to a database table

PLVlog provides the ps2db procedure to transfer the contents of the PL/SQL table log to the currently defined database log table. The header for this procedure is:

PROCEDURE ps2db;

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.

21.1.5.1 Toggling rollback activity

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:

PROCEDURE 

do_rollback;
PROCEDURE nodo_rollback;
FUNCTION 

rolling_back RETURN BOOLEAN;

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;

21.1.5.2 How PLVlog manages the transaction

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 21.1.5.6, "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.

21.1.5.3 Types of rollback activity

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.

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 21.1.5.6 .

21.1.5.4 Setting the default "rollback to" behavior

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.

To change the default "rollback to" savepoint, you can call any of the programs whose headers are shown below:

PROCEDURE rb_to (savepoint_in IN VARCHAR2 := c_none);
PROCEDURE 

rb_to_last;
PROCEDURE 

rb_to_default;

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.

1. Set the default savepoint to "no action:"

   PLVlog.rb_to (PLVlog.c_noaction);

   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.

2. Set the default savepoint to none:

   PLVlog.rb_to (PLVlog.c_none);

   or:

   PLVlog.rb_to;

   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.

3. Set the default savepoint to the default:

   PLVlog.rb_to (PLVlog.c_default);

   or:

   PLVlog.rb_to_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).

4. Set the default savepoint to the PLVlog initial value:

   PLVlog.rb_to (PLVlog.c_PLVlogsp);

   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.

5. Set the default savepoint to the last savepoint issued by PLVrb:

   PLVlog.rb_to (PLVlog.c_last);

   or:

   PLVlog.rb_to_last;

   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.

6. Set the default savepoint to a user-defined savepoint indicating the start of a new order transaction:

   PLVlog.rb_to ('new_order');

   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.

21.1.5.5 Specifying rollbacks when calling put_line

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:

1. 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);

2. 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.

3. 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.

4. 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).

21.1.5.6 Performing different kinds of rollbacks

PLVlog takes a different rollback action depending on the default savepoint value. This logic is encapsulated in the local do_rb procedure inside put_line :

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.

21.1.5.7 Setting the post-insert savepoint

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.

You can change this savepoint with a call to the set_sp procedure, whose header is:

PROCEDURE set_sp (savepoint_in IN VARCHAR2);