[Chapter 7] 7.3 DBMS_APPLICATION

7.3 DBMS_APPLICATION_INFO Examples

Oracle suggests that one way to extend DBMS_APPLICATION_INFO is to capture session performance statistics as part of the process of registering modules and actions. To demonstrate how this might be done, I have created a package called register_app.

7.3.1 About the register_app Package

The programs in register_app are very similar to those in DBMS_APPLICATION_INFO. Here is the package specification:

/* Filename on companion disk:

 register.sql */*
CREATE OR REPLACE PACKAGE register_app
IS
   /*
   || Enhances DBMS_APPLICATION_INFO by capturing performance
   || statistics when module, action, or client_info are set.
   ||
   || Statistics may be displayed in SQL*Plus for tracking and
   || debugging purposes.  A useful enhancement would be to 
   || extend this idea to a logging feature, so stats are logged
   || to a table for analysis.
   ||
   || Also enforces requirement that a module be registered before 
   || an action can be registered. 
   ||
   || Author:  John Beresniewicz, Savant Corp
   || Created: 09/01/97
   ||
   || Compilation Requirements:
   ||
   || SELECT on SYS.V_$MYSTAT
   || SELECT on SYS.V_$STATNAME
   ||
   || Execution Requirements:
   ||
   ||
   */

   /* registers the application module */
   PROCEDURE module
      (module_name_IN IN VARCHAR2
      ,action_name_IN IN VARCHAR2 DEFAULT 'BEGIN');

   /* registers the action within module */
   PROCEDURE action(action_name_IN IN VARCHAR2);

   /* registers additional application client information */ 
   PROCEDURE client_info(client_info_IN IN VARCHAR2);

   /* returns the currently registered module */
   FUNCTION current_module RETURN VARCHAR2;

   /* returns the currently registered action */
   FUNCTION current_action RETURN VARCHAR2;

   /* returns the currently registered client info */
   FUNCTION current_client_info RETURN VARCHAR2;

   /* sets stat display for SQL*Plus ON (TRUE) or OFF (FALSE) */
   PROCEDURE set_display_TF(display_ON_TF_IN IN BOOLEAN);

END register_app;

The module, action, and client_info programs of register_app correspond directly to the SET_MODULE, SET_ACTION, and SET_CLIENT_INFO programs of DBMS_APPLICATION_INFO; indeed, each of these programs eventually calls its counterpart. The difference is that the programs in register_app first collect session performance information and store it in a private package global record, before calling the appropriate DBMS_APPLICATION_INFO program.

7.3.2 The action Procedure

Here is the body of the action procedure:

/* Filename on companion disk: 

register.sql */*
PROCEDURE action(action_name_IN IN VARCHAR2)
IS
BEGIN

        /* 
        || raise error if trying to register an action when module
        || has not been registered
        */
        IF current_module IS NULL AND action_name_IN IS NOT NULL
        THEN
           RAISE_APPLICATION_ERROR(-20001, 'Module not registered');
        ELSE
           set_stats;
           SYS.DBMS_APPLICATION_INFO.SET_ACTION(action_name_IN);
        END IF;

END action;

Note that the action procedure is written to enforce the rule that an action can be registered only if a module has previously been registered. The action procedure also calls a procedure called set_stats. This procedure is private to the package, and does the work of collecting and saving resource statistics whenever new module, action, or client information is registered.

7.3.3 The set_stats Procedure

The set_stats procedure loads session performance information into a private global record named stat_rec. Here are the definitions of stat_rec and the PL/SQL record type on which it is based:

/* record type to hold performance stats */
TYPE stat_rectype IS RECORD
   (timer_hsecs  NUMBER := 0
   ,logical_rds  NUMBER := 0
   ,physical_rds NUMBER := 0
   );
/* private global to hold stats at begin of each module/action */
stat_rec stat_rectype;

Now let's take a look at the set_stats procedure:

/* Filename on companion disk: r

egister.sql/*
|| Gets current performance stats from V$MYSTAT and
|| sets the global record stat_rec.  If display_TF_ is TRUE
|| then uses DBMS_OUTPUT to display the stat differences
|| since last call to set_stats.
*/
PROCEDURE set_stats
IS

   temp_statrec   stat_rectype;
   diff_statrec   stat_rectype;
       
   /* 
   || Embedded inline function to retrieve stats by name
   || from V$MYSTAT.
   */
   FUNCTION get_stat(statname_IN IN VARCHAR2)
      RETURN NUMBER
   IS
      /* return value -9999 indicates problem */
      temp_stat_value  NUMBER := -9999;

      /* cursor retrieves stat value by name */
      CURSOR stat_val_cur(statname VARCHAR2)

      IS
         SELECT value 
            FROM sys.v_$mystat    S
               ,sys.v_$statname  N
         WHERE 
               S.statistic# = N.statistic#
            AND N.name = statname;

       BEGIN
          OPEN stat_val_cur(statname_IN);
          FETCH stat_val_cur INTO temp_stat_value;
          CLOSE stat_val_cur;
          RETURN temp_stat_value;

       EXCEPTION
          WHEN OTHERS THEN
             IF stat_val_cur%ISOPEN
             THEN 
                CLOSE stat_val_cur;
             END IF;
             RETURN temp_stat_value;
       END get_stat;

BEGIN

       /* 
       || load current values for performance statistics 
       */
       temp_statrec.timer_hsecs := DBMS_UTILITY.GET_TIME;
       temp_statrec.logical_rds := get_stat('session logical reads');
       temp_statrec.physical_rds := get_stat('physical reads');
   
       /* 
       || calculate diffs between current and previous stats 
       */
       diff_statrec.timer_hsecs := 
                temp_statrec.timer_hsecs - stat_rec.timer_hsecs;
       diff_statrec.logical_rds := 
                temp_statrec.logical_rds - stat_rec.logical_rds;
       diff_statrec.physical_rds := 
                temp_statrec.physical_rds - stat_rec.physical_rds;
   
       /* 
       || Both current module AND client info NULL indicates 
       || initialization for session and stats should not be displayed.
       */
       IF display_TF AND
          (current_module IS NOT NULL OR current_client_info IS NOT NULL)
       THEN
          DBMS_OUTPUT.PUT_LINE('Module: '||current_module);
          DBMS_OUTPUT.PUT_LINE('Action: '||current_action);
          DBMS_OUTPUT.PUT_LINE('Client Info: '||current_client_info);
          DBMS_OUTPUT.PUT_LINE('Stats:  '||
             'elapsed secs:'||
                       TO_CHAR(ROUND(diff_statrec.timer_hsecs/100,2))||
              ', physical reads: '||TO_CHAR(diff_statrec.physical_rds)||
              ', logical reads: '||TO_CHAR(diff_statrec.logical_rds) 
              );

        END IF;
      
        /* OK, now initialize stat_rec to current values */
        stat_rec := temp_statrec;

END set_stats;

The set_stats procedure logic is relatively straightforward:

Current values for the session performance statistics are gathered and the previous values (stored in the private global record stat_rec) are subtracted from them. These differences represent the changes in statistics since the last call to set_stats and are held in the record diff_statrec. Note that this works even for the initial call to set_stats because the declaration of stat_rectype assigns a default value of zero to all fields. Thus, on the first call, stat_rec will be initialized with zeros and diff_statrec will contain the current statistics.
The difference performance statistics are displayed using DBMS_OUTPUT if the display flag is set and this is not the first call to register application information.
Current values of the session performance statistics are saved in stat_rec for the next call to set_stats.

Exercise for the reader: Enhance the register_app package to log module and action performance statistics to a table for resource accounting. Be sure to allow for tracking by username and session.

7.3.4 The Information Procedures

The register_app package also contains three functions that return the currently registered information for the session. These functions invoke the DBMS_APPLICATION_INFO procedures READ_MODULE and READ_CLIENT_INFO and return the respective information. Procedures that return data in OUT parameters can often be encapsulated usefully with functions in this way. This promotes more terse and readable code, as illustrated by the following code excerpts from the register_app package:

/* Filename on companion disk: 

register.sql */*
/* returns the currently registered module */
FUNCTION current_module RETURN VARCHAR2;

/* returns the currently registered client info */
FUNCTION current_client_info RETURN VARCHAR2;

PROCEDURE set_stats
IS
...
IF display_TF AND
      (current_module IS NOT NULL OR current_client_info IS NOT NULL)
THEN
...
END set_stats;

7.3.5 Using the register_app Package

The following SQL*Plus script demonstrates how the register_app package can be used to register each step of a multistep batch process. The script displays the resource utilization statistics that have been collected for each step. While the script is executing, DBAs can monitor which step is currently running by querying V$SESSION for the session executing the script.

/* Filename on companion disk: 

regtest.sql  */*
rem ======================================================
rem REGTEST.SQL
rem
rem  SQL*Plus script to demonstrate the use of package 
rem  REGISTER_APP for tracking performance statistics
rem
rem ======================================================

set serveroutput on size 100000

set feedback off
rem ======================================================
rem  register module first with display OFF to
rem  initialize stats, then set display ON 
rem ======================================================
execute register_app.set_display_TF(FALSE);
execute register_app.module('REGTEST.SQL');
execute register_app.set_display_TF(TRUE);

set feedback on

rem ======================================================
rem  create a table my_dictionary copied from dictionary
rem ======================================================
execute register_app.action('CREATE');

CREATE TABLE my_dictionary
   (id, table_name, comments) 
TABLESPACE user_data2
AS
   SELECT rownum,A.* 
     FROM dictionary A;

rem ======================================================
rem  update one third of my_dictionary rows
rem ======================================================
execute register_app.action('UPDATE');

UPDATE my_dictionary
   SET comments = RPAD(comments,2000,'*')
 WHERE MOD(id,3) = 0;

rem ======================================================
rem  delete one third of my_dictionary rows
rem ======================================================
execute register_app.action('DELETE');

DELETE FROM my_dictionary
 WHERE MOD(id,3) = 1;

rem ======================================================
rem  drop table my_dictionary 
rem ======================================================
execute register_app.action('DROP');

DROP TABLE my_dictionary;

rem ======================================================
rem  unregister and display previous step stats 
rem ======================================================
execute register_app.module(null,null);

Here is sample output generated by the script:

SQL> @regtest
Module: REGTEST.SQL                                                                                 
Action: BEGIN                                                                                       
Client Info:                                                                                        
Stats:  elapsed secs: .15, physical reads: 0, logical reads: 0                                      

PL/SQL procedure successfully completed.

Table created.

Module: REGTEST.SQL                                                                                 
Action: CREATE                                                                                      
Client Info:                                                                                        
Stats:  elapsed secs: 15.93, physical reads: 137, logical reads: 8407                               

PL/SQL procedure successfully completed.

92 rows updated.

Module: REGTEST.SQL                                                                                 
Action: UPDATE                                                                                      
Client Info:                                                                                        
Stats:  elapsed secs: 9.32, physical reads: 8, logical reads: 2075                                  

PL/SQL procedure successfully completed.

93 rows deleted.

Module: REGTEST.SQL                                                                                 
Action: DELETE                                                                                      
Client Info:                                                                                        
Stats:  elapsed secs: .6, physical reads: 0, logical reads: 296                                     

PL/SQL procedure successfully completed.


Table dropped.

Module: REGTEST.SQL                                                                                 
Action: DROP                                                                                        
Client Info:                                                                                        
Stats:  elapsed secs: 5.36, physical reads: 35, logical reads: 356                                  

PL/SQL procedure successfully completed.

7.3.6 Covering DBMS_APPLICATION_INFO

Oracle suggests in the DBMS_APPLICATION_INFO package documentation that DBAs may want to develop a cover package called DBMS_APPLICATION_INFO in a schema other than SYS. By redirecting the public synonym DBMS_APPLICATION_INFO to point at this version of the package, any programs referencing DBMS_APPLICATION_INFO programs will use the new package. Any functional extensions to DBMS_APPLICATION_INFO in the cover package will be immediately picked up by programs using DBMS_APPLICATION_INFO. In this way, resource tracking like that demonstrated by the register_app package can be implemented globally for programs using DBMS_APPLICATION_INFO.

Instead of directly covering DBMS_APPLICATION_INFO with a package of the same name, I chose to create the register_app package. One reason for this: I prefer the shorter and more meaningful name register_app. New applications can call register_app directly and avoid the painfully long DBMS_APPLICATION_INFO package name. Another reason was that I wanted to extend the functionality of DBMS_APPLICATION_INFO with new programs, and thus the new package would not look identical to DBMS_APPLICATION_INFO. When covering an Oracle built-in package, it is good practice to create a package with an identical specification (or API) to that of the built-in.

We can actually cover DBMS_APPLICATION_INFO with a package that calls the register_app programs. In this way, the functionality of register_app is extended to programs that reference DBMS_APPLICATION_INFO directly, and we still have our new package to use for new programs.

The following code shows how DBMS_APPLICATION_INFO.SET_MODULE can be covered in this way:

CREATE OR REPLACE PACKAGE BODY DBMS_APPLICATION_INFO
IS
PROCEDURE set_module
    (module_name IN VARCHAR2
    ,action_name IN VARCHAR2)
IS
   register_app.module(module_name, action_name);
END set_module;

Notice that the SET_MODULE cover procedure is identical in signature to the program of the same name in the SYS version of the DBMS_APPLICATION_INFO package.

Q: Why must the cover package for DBMS_APPLICATION_INFO match all program signatures identically, including parameter names?

A: The program signatures in the cover package to DBMS_APPLICATION_INFO must match those in the SYS version of the package because existing calls to DBMS_APPLICATION_INFO could otherwise be compromised. It is necessary to match not only the number of parameters and their datatypes and modes (IN or OUT) but also the parameter names. The parameter names must match in order to preserve functionality in existing programs calling DBMS_APPLICATION_INFO using named notation. The following fragment illustrates code that will not work if the cover package does not preserve parameter names in the signature for the SET_MODULE procedure:

DECLARE
   module_var  VARCHAR2(64) := 'Program 1';
   action_var  VARCHAR2(64) := 'Transaction A';
BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE
      (module_name=>module_var
      ,action=>action_var);
END;

Q: What necessary precaution was taken in the register_app package to ensure that it could be used as part of a cover package for DBMS_APPLICATION_INFO?

A: All calls to DBMS_APPLICATION_INFO in the register_app package are fully qualified with the schema name (SYS). This way, when the public synonym DBMS_APPLICATION_INFO is redirected to point at the cover package, an infinite loop is avoided and the SYS version of the package is ultimately called.

Exercise for the reader: Create the full cover package for DBMS_APPLICATION_INFO using the register_app package.

7.3.7 Monitoring Application SQL Resource Consumption

When applications make use of DBMS_APPLICATION_INFO to register themselves, DBAs can monitor application usage and resource consumption through the V$SESSION and V$SQLAREA virtual tables. The following is a simple report summarizing SQL resource consumption data by application module and action. Such reports can serve a number of useful purposes, including the following:

Identifying tuning opportunities
Quantifying utilization levels by application component

Implementing chargeback schemes

/* Filename on companion disk: 

sqlarea.sql  */

rem ======================================================
rem SQLAREA.SQL
rem Simple report from V$SQLAREA on SQL resource
rem utilization by module and action
rem ======================================================

col module format a15
col action format a15
 
SELECT  module
       ,action
       ,SUM(buffer_gets) buffer_gets
       ,SUM(rows_processed) rows_processed
       ,SUM(disk_reads) disk_reads
  FROM  sys.v_$sqlarea
 WHERE module IS NOT NULL
   AND action IS NOT NULL
GROUP BY module, action;

The following output was generated by the script after regtest.sql had been executed several times:

SQL> @sqlarea

MODULE          ACTION           BUFFER_GETS  ROWS_PROCESSED  DISK_READS 
-------------   ---------------  -----------  --------------  ---------- 
REGTEST.SQL     BEGIN                   0             7            0  
REGTEST.SQL     CREATE                  0             7            0  
REGTEST.SQL     DELETE               1014           313            0  
REGTEST.SQL     DROP                    0             7            0  
REGTEST.SQL     UPDATE               6721           308           33  

5 rows selected.

7.3.8 Session Monitoring and Three-Tier Architectures

While writing this section on DBMS_APPLICATION_INFO, I had occasion to recommend the use of this package to help solve two real-world issues that came to my attention. In one case, an application had been written to call DBMS_SESSION.SET_SQL_TRACE and thus turn SQL tracing on for a session running the application.[ 1 ] The DBA wanted to know which sessions were being traced at any given time. I suggested the use of DBMS_APPLICATION_INFO.SET_CLIENT_INFO to put a message into the V$SESSION table indicating a tracing session. The procedure to set tracing could look something like this:

[1] Coding applications with the ability to set SQL tracing on and off is very good practice, as it can greatly assist in the detection of post-deployment runtime performance problems.

PROCEDURE set_trace (on_TF IN BOOLEAN)
IS
BEGIN
   IF on_TF
      THEN
         DBMS_APPLICATION_INFO.SET_CLIENT_INFO('TRACE ON');
      ELSE
         DBMS_APPLICATION_INFO.SET_CLIENT_INFO('');
   END IF;

   DBMS_SESSION.SET_SQL_TRACE(on_TF);
END set_trace;

In the second example, I was discussing with another DBA the difficult issue of tracking down specific users in the following types of applications:

Three-tier applications like Oracle WebServer where users do not connect to Oracle directly, but through proxy connections held by the application server.
Applications where all users connect to Oracle under a common username, and security and user-differentiation are maintained entirely within the application at runtime.

Both of these architectures make it difficult for the DBA to correlate specific end users with the database sessions they are currently using. In the first case, sessions are persistent and serve different users at different times -- and sometimes no user at all. In the second case, all user sessions connect to a common username and thus are indistinguishable (by username) in V$SESSION. Interestingly enough, these are both perfect opportunities to use DBMS_APPLICATION_INFO.SET_CLIENT_INFO. When users connect to the application, call a procedure like the set_user procedure in the example for DBMS_APPLICATION_INFO.SET_CLIENT_INFO. A better version of set_user would call register_app.client_info to enable performance statistics tracking for the application users.

7.3.9 Tracking Long-Running Processes

The SET_SESSION_LONGOPS procedure is an interesting addition to DBMS_APPLICATION_INFO first found in the Oracle8 version of the package. Oracle documentation makes it clear that the intended use of the procedure is to enable external tracking of the progress of long-duration operations through the new virtual table, V$SESSION_LONGOPS. However, I found SET_SESSION_LONGOPS rather nonintuitive and unwieldy to use.

One difficult concept is the reuse of the four rows in V$SESSION_LONGOPS based on unique combinations of context and stepid, and how this relates to the hint parameter, which is used to identify the row to modify. Context and stepid do not have to be unique among the rows in V$SESSION_LONGOPS, but setting a new context/stepid combination will always cause acquisition of a new row. Because multiple rows can be identical in context/stepid, they do not really form a key (along with the session SID) to the virtual table. The hint parameter to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS seems to be the only way to identify which row is currently being set, but there is no column in V$SESSION_LONGOPS corresponding to the hint. Thus it is actually impossible to externally identify with accuracy the row modified by the last call to the procedure. This defeated my efforts to write a READ_SESSION_LONGOPS procedure that takes a hint value in and reports the values for the row identified by that hint value.

Another usability issue with SET_SESSION_LONGOPS is that any values not set in the procedure call will be set to zero. Thus, if you want to increment different counter columns at different times in an application (for the same row in V$SESSION_LONGOPS), you must keep track of all counter values and pass them all in each time the procedure is called. Adding to the cumbersome nature of the long parameter list are the extremely long names of the package and procedure themselves. You really have to want, or, as is more likely, need to call SET_SESSION_LONGOPS in order to use it!

These usability issues seemed to provide an opportunity to improve ease-of-use through encapsulation. I decided to build a package called longops to offer some relief. Here is the package specification for longops:

/* Filename on companion disk:

 longops.sql */*
CREATE OR REPLACE PACKAGE longops
IS
   /*
   || Enhances DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS
   || by allowing individual columns to be updated without
   || passing all parameter values.
   ||
   || Author:  John Beresniewicz, Savant Corp
   || Created: 09/08/97
   ||
   || Compilation Requirements:
   || SELECT on SYS.V_$SESSION_LONGOPS
   ||
   || Execution Requirements:
   ||
   ||
   */

   /* returns a new V$SESSION_LONGOPS row index */
   FUNCTION new_row RETURN BINARY_INTEGER;

   /* returns the last row index used */
   FUNCTION current_row RETURN BINARY_INTEGER;

   /* makes a new row the current row */
   PROCEDURE set_current_row
      (row_idx_IN IN BINARY_INTEGER);

   /* 
   || Covers DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS
   */
   PROCEDURE set_row
      (hint_IN IN BINARY_INTEGER
      ,context_IN IN NUMBER DEFAULT 0
      ,stepid_IN IN NUMBER DEFAULT 0
      ,stepsofar_IN IN NUMBER DEFAULT 0
      ,steptotal_IN IN NUMBER DEFAULT 0
      ,sofar_IN IN NUMBER DEFAULT 0
      ,totalwork_IN IN NUMBER DEFAULT 0
      ,appdata1_IN IN NUMBER DEFAULT 0
      ,appdata2_IN IN NUMBER DEFAULT 0
      ,appdata3_IN IN NUMBER DEFAULT 0);

   /*
   || Updates a single row in V$SESSION_LONGOPS
   || preserving values in columns corresponding
   || to parameters passed as NULL.
   */
   PROCEDURE update_row
      (hint_IN IN BINARY_INTEGER DEFAULT current_row
      ,context_IN IN NUMBER DEFAULT NULL
      ,stepid_IN IN NUMBER DEFAULT NULL
      ,stepsofar_IN IN NUMBER DEFAULT NULL
      ,steptotal_IN IN NUMBER DEFAULT NULL
      ,sofar_IN IN NUMBER DEFAULT NULL
      ,totalwork_IN IN NUMBER DEFAULT NULL
      ,appdata1_IN IN NUMBER DEFAULT NULL
      ,appdata2_IN IN NUMBER DEFAULT NULL
      ,appdata3_IN IN NUMBER DEFAULT NULL);

END longops;

The real key to the package is the update_row procedure. This procedure allows the user to update individual columns in V$SESSION_LONGOPS for a given row without zeroing out the other columns. It does this by keeping a copy of the V$SESSION_LONGOPS rows that have been modified in a private PL/SQL table called my_longops_tab. Here is the definition of my_longops_tab:

   TYPE longops_tabtype IS TABLE OF sys.v_$session_longops%ROWTYPE
      INDEX BY BINARY_INTEGER;
   my_longops_tab longops_tabtype;

The current_row function and set_current_row procedure are used to maintain a context of which row is currently being modified. The presumption is that most users of SET_SESSION_LONGOPS will concentrate on a single row in V$SESSION_LONGOPS at a time. The set_row procedure covers SET_SESSION_LONGOPS but additionally saves the data to my_longops_tab.

The body of the update_row procedure looks like this:

PROCEDURE update_row
      (hint_IN IN BINARY_INTEGER DEFAULT current_row
      ,context_IN IN NUMBER DEFAULT NULL
      ,stepid_IN IN NUMBER DEFAULT NULL
      ,stepsofar_IN IN NUMBER DEFAULT NULL
      ,steptotal_IN IN NUMBER DEFAULT NULL
      ,sofar_IN IN NUMBER DEFAULT NULL
      ,totalwork_IN IN NUMBER DEFAULT NULL
      ,appdata1_IN IN NUMBER DEFAULT NULL
      ,appdata2_IN IN NUMBER DEFAULT NULL
      ,appdata3_IN IN NUMBER DEFAULT NULL)
   IS
      temp_hint_IN BINARY_INTEGER := hint_IN;
   BEGIN
      /*
      || First update saved row in my_longops_tab, any 
      || parameters which are NULL will not change the
      || saved row.
      */
      my_longops_tab(hint_IN).context := NVL(context_IN,
               my_longops_tab(hint_IN).context);
      my_longops_tab(hint_IN).stepid := NVL(stepid_IN,
               my_longops_tab(hint_IN).stepid);
      my_longops_tab(hint_IN).stepsofar := NVL(stepsofar_IN,
               my_longops_tab(hint_IN).stepsofar);
      my_longops_tab(hint_IN).steptotal := NVL(steptotal_IN,
               my_longops_tab(hint_IN).steptotal);
      my_longops_tab(hint_IN).sofar := NVL(sofar_IN,
               my_longops_tab(hint_IN).sofar);
      my_longops_tab(hint_IN).totalwork := NVL(totalwork_IN,
               my_longops_tab(hint_IN).totalwork);
      my_longops_tab(hint_IN).application_data_1 := NVL(appdata1_IN,
               my_longops_tab(hint_IN).application_data_1);
      my_longops_tab(hint_IN).application_data_2 := NVL(appdata2_IN,
               my_longops_tab(hint_IN).application_data_2);
      my_longops_tab(hint_IN).application_data_3 := NVL(appdata3_IN,
               my_longops_tab(hint_IN).application_data_3);

      /*
      || Now call DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS
      || passing all parameters from the row in my_longops_tab.
      */
      DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS
         (hint=>temp_hint_IN
         ,context=>my_longops_tab(hint_IN).context
         ,stepid=>my_longops_tab(hint_IN).stepid
         ,stepsofar=>my_longops_tab(hint_IN).stepsofar
         ,steptotal=>my_longops_tab(hint_IN).steptotal
         ,sofar=>my_longops_tab(hint_IN).sofar
         ,totalwork=>my_longops_tab(hint_IN).totalwork
         ,application_data_1=>
                  my_longops_tab(hint_IN).application_data_1
         ,application_data_2=>
                  my_longops_tab(hint_IN).application_data_2
         ,application_data_3=>
                  my_longops_tab(hint_IN).application_data_3
         );

      /* set the current row */
      set_current_row(hint_IN);

   END update_row;

The update_row procedure is pretty straightforward. One subtlety is that the hint_IN parameter defaults to the function current_row. This allows us to call update_row without even passing in a row identifier as long as we want to modify the same row as last time. Using the longops package, the example for DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS can be rewritten as follows:

/* Filename on companion disk:

 apinex3.sql  */*
BEGIN
   -- get new row in V$SESSION_LONGOPS and set totalwork
   longops.set_row(longops.new_row,totalwork_IN=>1000);   

   -- Do operation 1000 times and record
   FOR i IN 1..1000
   LOOP
      -- update sofar each time 
      longops.update_row(sofar_IN=>i);

      -- update stepsofar every 100 iterations
      IF MOD(i,100) = 0
      THEN
         longops.update_row(stepsofar_IN=>i/100);
      END IF;
   END LOOP;
END;

This code is much more readable than the earlier example. The calls are shorter in length, yet easier to understand. Overall readability is also improved by being able to update columns individually and not being forced to overload each call with a long list of saved parameter values.