Oracle | DBMS_APPLICATION

Oracle DBMS_APPLICATION_INFO Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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

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