Oracle DBMS_APPLICATION_INFO
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Multiple APIs for writing application information to V$SESSION, monitoring application progress, and creating parameterized views.
AUTHID DEFINER
Constants
Name Data Type Value
set_session_longops_nohint BINARY_INTEGER -1
Dependencies
DBMS_BACKUP_RESTORE KUPW$WORKER WWV_FLOW_PROCESS
DBMS_STATS V$SESSION WWV_FLOW_SC_TRANSACTIONS
KUPM$MCP WWV_FLOW WWV_FLOW_SW_SCRIPT
KUPV$FT WWV_FLOW_PAGE WWV_FLOW_VALIDATION
Documented Yes
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC and APEX_040200
Source {ORACLE_HOME}/rdbms/admin/dbmsapin.sql
Subprograms
 
READ_CLIENT_INFO
Read the value of the client_info field of the current session dbms_application_info.read_client_info(client_info OUT VARCHAR2(64));
col client_info format a20

SELECT schemaname, osuser, client_info
FROM gv$session
WHERE service_name NOT LIKE '%BACK%';

exec dbms_application_info.set_client_info('B%');

set serveroutput on

DECLARE
 x VARCHAR2(100);
BEGIN
  dbms_application_info.read_client_info(x);
  dbms_output.put_line(x);
END;
/

exec dbms_application_info.set_client_info('747');

SELECT schemaname, osuser, client_info
FROM gv$session
WHERE service_name NOT LIKE '%BACK%';

DECLARE
 x VARCHAR2(100);
BEGIN
  dbms_application_info.read_client_info(x);
  dbms_output.put_line(x);
END;
/

-- the following will not work but try it so that you understand why
-- you can not use a stored procedure in a WHERE clause

CREATE OR REPLACE VIEW airplanes_view AS
SELECT *
FROM airplanes
WHERE program_id = dbms_application_info.read_client_info(x);

-- wrap the stored procedure so that it presents itself as a function
CREATE OR REPLACE FUNCTION app_info_wrapper RETURN VARCHAR2 IS
 x VARCHAR2(64);
BEGIN
  dbms_application_info.read_client_info(x);
  RETURN x;
END app_info_wrapper;
/

-- now you can create the view
CREATE OR REPLACE VIEW airplanes_view AS
SELECT *
FROM airplanes
WHERE program_id = app_info_wrapper;

SELECT * FROM airplanes_view
WHERE rownum < 1001;

exec dbms_application_info.set_client_info('777');

SELECT * FROM airplanes_view
WHERE rownum < 1001;
 
READ_MODULE
Reads the values of the module and action fields of the current session dbms_application_info.read_module(
module_name OUT VARCHAR2(64),
action_name OUT VARCHAR2(64));
SELECT schemaname, osuser, module
FROM gv$session
WHERE service_name NOT LIKE '%BACK%';

set serveroutput on

DECLARE
 mod_in  VARCHAR2(64);
 act_in  VARCHAR2(64);

 mod_out VARCHAR2(64);
 act_out VARCHAR2(64);

 display_str VARCHAR2(200);
BEGIN
  mod_in := 'Test Module';
  act_in := 'Test Action';
  dbms_application_info.set_module(mod_in, act_in);

  dbms_lock.sleep(5);

  dbms_application_info.read_module(mod_out, act_out);

  display_str := 'Module Is '||mod_out||' and Action is '||act_out;

  dbms_output.put_line(display_str);
END;
/

SELECT schemaname, osuser, module
FROM gv$session
WHERE service_name NOT LIKE '%BACK%';
 
SET_ACTION
Sets the name of the current action within the current module dbms_application_info.set_action(action_name IN VARCHAR2(64));
desc gv$session

SELECT schemaname, osuser, action
FROM gv$session
WHERE service_name NOT LIKE '%BACK%';

exec dbms_application_info.set_action('Load Departments');

SELECT schemaname, osuser, action
FROM gv$session
WHERE service_name NOT LIKE '%BACK%';
 
SET_CLIENT_INFO
Set Client Info Field For The Session dbms_application_info.set_client_info(client_info IN VARCHAR2(64));
CREATE OR REPLACE VIEW btest AS
SELECT object_name
FROM all_objects
WHERE object_name LIKE userenv('client_info');

SELECT * FROM btest;

exec dbms_application_info.set_client_info('B%');

SELECT * FROM btest;

--====================================

CREATE OR REPLACE VIEW vair AS
SELECT *
FROM airplanes
WHERE program_id = userenv('client_info');

SELECT * FROM vair;

exec dbms_application_info.set_client_info('747');

SELECT * FROM vair;
 
SET_MODULE
Sets the name of the module that is currently running dbms_application_info.set_module(
module_name IN VARCHAR2(64),
action_name IN VARCHAR2(64));
See READ_MODULE Demo Above
 
SET_SESSION_LONGOPS
Sets a row in the GV$SESSION_LONGOPS view dbms_application_info.set_session_longops(
rindex      IN OUT BINARY_INTEGER,
slno        IN OUT BINARY_INTEGER,
op_name     IN     VARCHAR2(64) DEFAULT NULL,
target      IN     BINARY_INTEGER DEFAULT 0,
context     IN     BINARY_INTEGER DEFAULT 0,
sofar       IN     NUMBER DEFAULT 0,
totalwork   IN     NUMBER DEFAULT 0,
target_desc IN     VARCHAR2(32) DEFAULT 'unknown_target',
units       IN     VARCHAR2(32) DEFAULT NULL);

rindex constant to start a new row
set_session_longops_nohint constant BINARY_INTEGER := -1;
use returned value from previous call to reuse a row

do not use slno ... for internal use by Oracle

target is the object number being worked on

sofar is any number indicating progress ... so far

totalwork a best guess as to the 100% value ... on completion

units used for sofar and totalwork
CREATE TABLE test (
testcol NUMBER(10));

-- Session 1
SELECT sid FROM v$mystat WHERE rownum = 1;
-- use this sid number in the session 2 query below

DECLARE
 rindex  BINARY_INTEGER := dbms_application_info.set_session_longops_nohint;
 slno    BINARY_INTEGER;
 sofar   NUMBER(6,2);
 target  BINARY_INTEGER;
 totwork NUMBER := 100;
BEGIN
  SELECT object_id
  INTO target
  FROM all_objects
  WHERE object_name = 'TEST';

  FOR i IN 1 .. totwork
  LOOP
    sofar := i;
    dbms_application_info.set_session_longops(rindex, slno,
    'MLIB', target, 0, sofar, 100, 'Pct Complete');

    INSERT INTO test VALUES (i);

    dbms_lock.sleep(0.25);
  END LOOP;
  COMMIT;
END;
/

-- Session 2 substitute the sid returned above from session 1
SELECT sid, serial#, schemaname
FROM gv$session;

SELECT start_time, sofar, totalwork, time_remaining, elapsed_seconds
FROM gv$session_longops
WHERE sid = 132
AND serial# = 1571;
 
Demo
Set Action Demo CREATE TABLE test (
testcol NUMBER(10));

-- session 1
DECLARE
 mod_name VARCHAR2(64);
 act_name VARCHAR2(64);
BEGIN
  mod_name := 'read mod';
  act_name := 'inserting';
  dbms_application_info.set_module(mod_name, act_name);

  FOR x IN 1..5
  LOOP
    FOR i IN 1 ..60
    LOOP
      INSERT INTO test VALUES (i);
      COMMIT;
      dbms_lock.sleep(1);
    END LOOP;

    act_name := 'deleting';
    dbms_application_info.set_action(act_name);
    FOR i IN 1 ..60
    LOOP
      DELETE FROM test WHERE testcol = i;
      COMMIT;
      dbms_lock.sleep(1);
    END LOOP;
  END LOOP;
END;
/

-- session 2
col module format a20
col action format a20

SELECT module, action
FROM gv$session;

SELECT module, action
FROM gv$sqlarea;

SELECT sql_text, disk_reads, module, action
FROM gv$sqlarea
WHERE action = 'deleting';

Related Topics
Packages
Procedures
SYS_CONTEXT

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved