#

| Chapter 7 Defining an Application Profile |

## 7.2 DBMS_APPLICATION_INFO Interface

This section describes all the programs available in the DBMS_APPLICATION_INFO package.

### 7.2.1 The DBMS_APPLICATION_INFO.READ_CLIENT_INFO procedure

The READ_CLIENT_INFO procedure returns the currently registered client information for the session. The program header is,

>     PROCEDURE DBMS_APPLICATION_INFO.READ_CLIENT_INFO
>         (client_info OUT VARCHAR2);

where the client_info parameter contains the client information currently registered in V$SESSION.

The program does not raise any exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.

#### 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 The DBMS_APPLICATION_INFO.READ_MODULE procedure

The READ_MODULE procedure returns the currently registered module and action names for the session. Here's the program header:

>     PROCEDURE DBMS_APPLICATION_INFO.READ_MODULE
>        (module_name OUT VARCHAR2
>        ,action_name OUT VARCHAR2);

Parameters are summarized in the following table.

Parameter | Description
--- | ---
module_name | Name of the module currently registered in V$SESSION
action_name | Name of the action currently registered in V$SESSION

The READ_MODULE procedure does not raise any exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.

#### 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 The DBMS_APPLICATION_INFO.SET_ACTION procedure

The SET_ACTION procedure is used to set, or register, the current transaction or logical unit of work currently executing within the module. The registered action name appears in the ACTION column of the V$SESSION and V$SQLAREA virtual tables. The program header is,

>     PROCEDURE DBMS_APPLICATION_INFO.SET_ACTION
>         (action_name IN VARCHAR2);

where the action_name parameter provides the name of the action to register into V$SESSION.

The SET_ACTION procedure does not raise any exceptions.

#### 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 The DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure

The SET_CLIENT_INFO procedure is used to set, or register, additional client information about the user session. The registered client information appears in the CLIENT_INFO column of the V$SESSION virtual table. The header for this program is,

>     PROCEDURE DBMS_APPLICATION_INFO.SET_CLIENT_INFO
>         (client_info IN VARCHAR2);

where the client_info parameter specifies the client information to register into V$SESSION.

The program does not raise any exceptions.

#### 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 The DBMS_APPLICATION_INFO.SET_MODULE procedure

The SET_MODULE procedure is used to set, or register, a name for the program that the user is currently executing and, optionally, an action name for the current transaction within the program. Registered module and action names appear in the MODULE and ACTION columns of the V$SESSION and V$SQLAREA virtual tables. Here's the header for this procedure:

>     PROCEDURE DBMS_APPLICATION_INFO.SET_MODULE
>         (module_name IN VARCHAR2
>         ,action_name IN VARCHAR2);

Parameters are summarized in the following table.

Parameter | Description
--- | ---
module_name | Name of the module to register into V$SESSION
action_name | Name of the action to register into V$SESSION

The SET_MODULE procedure does not raise any exceptions.

#### 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 The DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure

The SET_SESSION_LONGOPS procedure is used to track the progress of long-running operations by allowing the entry and modification of data in the V$SESSION_LONGOPS virtual table. Here's the header for the program:

>     PROCEDURE DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS
>         (hint IN OUT BINARY_INTEGER
>         ,context IN NUMBER DEFAULT 0
>         ,stepid IN NUMBER DEFAULT 0
>         ,stepsofar IN NUMBER DEFAULT 0
>         ,steptotal IN NUMBER DEFAULT 0
>         ,sofar IN NUMBER DEFAULT 0
>         ,totalwork IN NUMBER DEFAULT 0
>         ,application_data_1 IN NUMBER DEFAULT 0
>         ,application_data_2 IN NUMBER DEFAULT 0
>         ,application_data_3 IN NUMBER DEFAULT 0);

The parameters are summarized in the following table.

Parameter | Description
--- | ---
hint | Token representing the row to update
context | Any number representing the context
stepid | Any number representing the stepid
stepsofar | Any number
steptotal | Any number
sofar | Any number
totalwork | Any number
application_data_1 | Any number
application_data_2 | Any number
application_data_3 | Any number

The program does not raise any exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.

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

7.1 Getting Started with DBMS_APPLICATION_INFO |  | 7.3 DBMS_APPLICATION_INFO Examples

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.