Oracle | DBMS_APPLICATION

Oracle DBMS_APPLICATION_INFO Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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_AUTO_INDEX_INTERNAL	DBMS_STATS	KUPW$WORKER
DBMS_BACKUP_RESTORE	DBMS_SWAT_ARM_INTERNAL	ONAME
DBMS_GSM_CLOUDADMIN	KUPM$MCP	PNAME
DBMS_GSM_UTILITY	KUPV$FT

Documented Yes: Packages and Types Reference

First Available version 7

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsapin.sql

Subprograms

READ_CLIENT_INFO	SET_CLIENT_INFO	SET_SESSION_LONGOPS
READ_MODULE	SET_MODULE	Demo
SET_ACTION

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';