7.2.1.1 Example

The following function calls DBMS_APPLICATION_INFO.READ_CLIENT_INFO and returns the client information. This function is part of the register_app package discussed in "Section 7.3, "DBMS_APPLICATION_INFO Examples "" later in this chapter.

FUNCTION current_client_info RETURN VARCHAR2
IS
   /*
   || calls DBMS_APPLICATION_INFO.READ_CLIENT_INFO
   || and returns the client info 
   */
temp_client_info VARCHAR2(64);

BEGIN
   SYS.DBMS_APPLICATION_INFO.READ_CLIENT_INFO
      (temp_client_info);

   RETURN temp_client_info;
END current_client_info;

In this example, I have fully qualified the package name with the package owner (SYS), insuring that the SYS version of the package is called. This is not normally necessary, as there is (usually) a public synonym pointing to SYS.DBMS_APPLICATION_INFO. The reason for using a fully qualified reference in this specific case is discussed in "Section 7.3.6, "Covering DBMS_APPLICATION_INFO"."

7.2.2.1 Example

The following function calls DBMS_APPLICATION_INFO.READ_MODULE and returns the value of the current action. This function is part of the register_app package discussed in "Section 7.3."

   FUNCTION current_action RETURN VARCHAR2
   IS
      /*
      || calls DBMS_APPLICATION_INFO.READ_MODULE
      || and returns the action name 
      */
      temp_module_name VARCHAR2(64);
      temp_action_name VARCHAR2(64);

   BEGIN
      SYS.DBMS_APPLICATION_INFO.READ_MODULE
         (temp_module_name, temp_action_name);
   
      RETURN temp_action_name;
   END current_action;

See the section "Section 7.3.6" for an explanation of why the procedure call is qualified by SYS, the package owner's name.

7.2.3.1 Restrictions

Note the following restrictions on calling SET_ACTION:

• The action_name parameter is limited to 32 bytes. Longer values will be truncated to this maximum size.

• The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

7.2.3.2 Example

The following procedure could be part of an application that maintains corporate departmental information:

/* Filename on companion disk: apinex1.sql  */*
CREATE OR REPLACE PROCEDURE drop_dept
   (deptno_IN IN NUMBER
   ,reassign_deptno_IN IN NUMBER)
IS
   temp_emp_count  NUMBER;
BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE
      (module_name => 'DEPARTMENT FIXES'
      ,action_name => null);

   -- first check dept for employees

   DBMS_APPLICATION_INFO.SET_ACTION
      (action_name => 'CHECK EMP');

   SELECT COUNT(*) 
     INTO temp_emp_count
     FROM emp
    WHERE deptno = deptno_IN;

   -- reassign any employees 

   IF temp_emp_count >0
   THEN
      DBMS_APPLICATION_INFO.SET_ACTION
         (action_name => 'REASSIGN EMPLOYEES');
   
      UPDATE emp
         SET deptno = reassign_deptno_IN
       WHERE deptno = deptno_IN;
   END IF;

   -- OK, now drop the department

   DBMS_APPLICATION_INFO.SET_ACTION
      (action_name => 'DROP DEPT');

   DELETE FROM dept WHERE deptno = deptno_IN;

   COMMIT;

   DBMS_APPLICATION_INFO.SET_MODULE(null,null);

EXCEPTION
   WHEN OTHERS THEN
      DBMS_APPLICATION_INFO.SET_MODULE(null,null);
END drop_dept;

Notice in this example that DBMS_APPLICATION_INFO is called three times to distinguish between the three steps involved in the process of dropping the department. This gives a very fine granularity to the level at which the application can be tracked.

7.2.3.3 Recommendations for usage

Note the following recommendations for using the SET_ACTION procedure:

• Set the action name to a name that can identify the current transaction or logical unit of work within the module.

• When the transaction terminates, call SET_ACTION and pass a NULL value for the action_name parameter. In case subsequent transactions do not register using DBMS_APPLICATION_INFO, passing the NULL value ensures that they are not incorrectly counted as part of the current action. As in the example, if the program handles exceptions, the exception handler should reset the action information.

7.2.4.1 Restrictions

Note the following restrictions on calling SET_CLIENT_INFO:

• The client_info parameter is limited to 64 bytes. Longer values will be truncated to this maximum size.

• The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

7.2.4.2 Example

The following procedure could be utilized by an application in which all sessions connect to a common Oracle username and security is handled within the application. This would allow the DBA to externally distinguish between user sessions in V$SESSION by examining the CLIENT_INFO column.

/* Filename on companion disk: apinex1.sql  */*
CREATE OR REPLACE PROCEDURE set_user(app_user_IN IN VARCHAR2)
IS
BEGIN
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO(app_user_IN);
END set_user;

Suppose that users JOE SMITH and SALLY DALLY log into the application, which connects to Oracle as the user OPBIP. If the application calls the set_user procedure at login, we can distinguish between the database sessions for Joe and Sally in V$SESSION as follows:

SQL> SELECT sid, username, client_info
  2    FROM v$session
  3   WHERE username='OPBIP';

      SID USERNAME   CLIENT_INFO
--------- ---------- --------------------
       14 OPBIP      JOE SMITH
       24 OPBIP      SALLY DALLY

7.2.5.1 Restrictions

Note the following restrictions on calling SET_MODULE:

• The module_name parameter is limited to 48 bytes, and action_name is limited to 32 bytes. Longer values for either parameter will be truncated to their respective maximum sizes.

• The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

7.2.5.2 Example

The following procedure could be part of an application that maintains employee data:

/* Filename on companion disk: apinex1.sql  */*
CREATE OR REPLACE PROCEDURE award_bonus
   (empno_IN IN NUMBER
   ,pct_IN IN NUMBER)
IS
BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE
      (module_name => 'EMPLOYEE UPDATE'
      ,action_name => 'AWARD BONUS');

   UPDATE emp
      SET sal = sal*(1+pct_IN/100)
    WHERE empno = empno_IN;

   COMMIT;

   DBMS_APPLICATION_INFO.SET_MODULE(null,null);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_APPLICATION_INFO.SET_MODULE(null,null);
END award_bonus;

Oracle recommends that the module name correspond to a recognizable name for the program or logical application unit that is currently executing. Examples Oracle provides include the name of the form executing in a Forms application and the name of a SQL script executing under SQL*Plus.

7.2.5.3 Recommendations for usage

Note the following recommendations for using the SET_MODULE procedure:

• Set the action name to one that can identify the current transaction or logical unit of work within the module.

• When the module terminates, call the SET_MODULE procedure and pass in NULL values for both parameters. In the event that subsequent transactions and programs do not register using DBMS_APPLICATION_INFO, they won't be incorrectly counted as part of the current module. As in the example, if the program handles exceptions, the exception handler should reset the module and action information.

7.2.6.1 Example

The following script loops 1000 times and sets values in the V$SESSION_LONGOPS table as follows:

• The totalwork value is set to 1000.

• The sofar column is incremented for every iteration.

• The stepsofar column is incremented every 100 iterations.

  /* Filename on companion disk: apinex2.sql  */

DECLARE
   longops_row BINARY_INTEGER:=
      DBMS_APPLICATION_INFO.set_session_longops_nohint;
   step_number  NUMBER:=0;
BEGIN
   -- get new row in V$SESSION_LONGOPS
   DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS
      (hint => longops_row);

   -- Do operation 1000 times and record
   FOR i IN 1..1000
   LOOP
      DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS
         (hint => longops_row
         ,sofar => i
         ,totalwork => 1000
         ,stepsofar => step_number);

      -- increment step every 100 iterations
      IF MOD(i,100) = 0
      THEN
         step_number := i/100;
      END IF;
   END LOOP;
END;
/

After executing the previous PL/SQL block, the following SQL shows the results recorded in V$SESSION_LONGOPS. Notice that the COMPNAM column has been updated by Oracle to indicate that DBMS_APPLICATION_INFO was used to set the row. Also notice that Oracle sets rows in V$SESSION_LONGOPS for internal operations like table scans, and sorts.

SELECT sid
      ,compnam
      ,stepsofar
      ,sofar
      ,totalwork
  FROM v$session_longops
 WHERE sid = my_session.sid;


SID  COMPNAM                   STEPSOFAR     SOFAR TOTALWORK
---- ------------------------- --------- --------- ---------
  16 Table Scan                        0         0         1
  16 Sort Progression                  0         1         1
  16 dbms_application_info             9      1000      1000

3 rows selected.

Each session is allocated a maximum of four rows in the V$SESSION_LONGOPS virtual table for tracking long operations. Rows are identified by the combination of context and stepid. If calls to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS are made with more than four distinct combinations of context and stepid, rows will be re-used in least-recently-used order.

All of the parameters except hint correspond directly to like-named columns in the V$SESSION_LONGOPS virtual table. While there are no restrictions on values stored in these columns, Oracle makes the following suggestions as a way of organizing information about the progress of long running operations:

stepsofar

If the long-running operation consists of distinct individual steps, the amount of work which has been done so far for this step.

steptotal

If the long-running operation consists of distinct individual steps, the total amount of work expected to be done in this step.

sofar

The amount of work that has been done so far.

totalwork

The total amount of work expected to be done in this long-running operation.

application_data_1, application_data_2, application_data_3

Any numbers the client wishes to store.

Also note that all parameters to SET_SESSION_LONGOPS (except hint) default to zero. This means that calls to the procedure need not specify values for all parameters, which is convenient in the case of such a long parameter list. However, it also means that any unspecified parameters in a call to SET_SESSION_LONGOPS will have their corresponding columns in V$SESSION_LONGOPS set to zero for that row after the call, which may not be the desired behavior.