[Chapter 7] 7.2 DBMS_APPLICATION_INFO Interface Chapter 7Defining 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 procedureThe 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 ExampleThe 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 procedureThe 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.ParameterDescriptionmodule_nameName of the module currently registered in V$SESSIONaction_nameName of the action currently registered in V$SESSIONThe READ_MODULE procedure does not raise any exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.7.2.2.1 ExampleThe 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 procedureThe 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 RestrictionsNote 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 ExampleThe 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 usageNote 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 procedureThe 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 ExampleThe 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 DALLY7.2.5 The DBMS_APPLICATION_INFO.SET_MODULE procedureThe 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.ParameterDescriptionmodule_nameName of the module to register into V$SESSIONaction_nameName of the action to register into V$SESSIONThe 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 ExampleThe 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 usageNote 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 procedureThe 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.ParameterDescriptionhintToken representing the row to updatecontextAny number representing the contextstepidAny number representing the stepidstepsofarAny numbersteptotalAny numbersofarAny numbertotalworkAny numberapplication_data_1Any numberapplication_data_2Any numberapplication_data_3Any numberThe program does not raise any exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.7.2.6.1 ExampleThe 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.totalworkThe 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.