CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_APPLICATION_INFO

Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsapin.sql
First Available 7.3.4
Constants
Name Data Type Value
set_session_longops_nohint BINARY_INTEGER -1
Dependencies
DBMS_BACKUP_RESTORE EM_PING KUPV$FT
DBMS_STATS EM_SEVERITY_REPOS KUPW$WORKER
EMD_CRONOS_ADMIN GV_$SESSION WWV_FLOW
EMD_LOADER GV_$SESSION_LONGOPS WWV_FLOW_PAGE_CACHE_API
EMD_MAINTENANCE GV_$SQLAREA WWV_FLOW_SC_TRANSACTIONS
EMD_NOTIFICATION KUPM$MCP WWV_FLOW_SW_SCRIPT
 
READ_CLIENT_INFO

Read the value of the client_info field of the current session
dbms_application_info.set_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(48),
action_name OUT VARCHAR2(32));
SELECT schemaname, osuser, module
FROM gv$session
WHERE service_name NOT LIKE '%BACK%';

set serveroutput on

DECLARE
 mod_in  VARCHAR2(48);
 act_in  VARCHAR2(32);

 mod_out VARCHAR2(48);
 act_out VARCHAR2(32);

 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(32));
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_objs
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(48),
action_name IN VARCHAR2(32));
See READ_MODULE demo
 
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 proress ... 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 DISTINCT sid FROM gv$mystat;
-- use this sid number in the session 2 query below

DECLARE
 rindex  BINARY_INTEGER;
 slno    BINARY_INTEGER;
 sofar   NUMBER(6,2);
 target  BINARY_INTEGER;
 totwork NUMBER := 100;
BEGIN
  rindex := dbms_application_info.set_session_longops_nohint;

  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,
    'PSOUG', 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;
 
DBMS_APPLICATION_INFO Demo

Set Action Demo
CREATE TABLE test (
testcol NUMBER(10));

-- session 1
DECLARE
 mod_name VARCHAR2(48);
 act_name VARCHAR2(32);
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
Procedures
SYS_CONTEXT
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [54 users online]    © 2010 psoug.org