| 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_longop | WWV_FLOW_SC_TRANSACTIONS | | EMD_MAINTENANCE | gv_$sqlarea | WWV_FLOW_SW_SCRIPT | | EMD_NOTIFICATION | KUPM$MCP | |
|
|
| 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)); |
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');
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 clauseCREATE 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 functionCREATE 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;
exec dbms_application_info.set_client_info('777');
SELECT * FROM airplanes_view; |
|
| 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)); |
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;
/ |
|
| SET_ACTION |
| Sets the name of the current action within the current module | dbms_application_info.set_action(action_name IN VARCHAR2(32)); |
| exec dbms_application_info.set_action('Load Departments'); |
|
| 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)); |
| exec dbms_application_info.set_module('LOAD_TAB', 'Load Emp'); |
|
|
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_objs
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 1SELECT sid, serial#, schemaname
FROM gv$session;
SELECT start_time, sofar, totalwork, time_remaining, elapsed_seconds
FROM gv$session_longops
WHERE sid = 140
AND serial# = 266; |
|
| 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'; |