| 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 |
EMD_NOTIFICATION |
KUPV$FT |
| DBMS_STATS |
EM_PING |
KUPW$WORKER |
| DSCM_UTILS |
EM_SEVERITY_REPOS |
WWV_FLOW |
| EMD_CHRONOS_ADMIN |
GV_$SESSION |
WWV_FLOW_PAGE_CACHE_API |
| EMD_LOADER |
GV_$SESSION_LONGOPS |
WWV_FLOW_SC_TRANSACTIONS |
| EMD_MAINTENANCE |
GV_$SQLAREA |
WWV_FLOW_SW_SCRIPT |
| DMD_MAINT_UTIL |
KUPM$MCP |
|
|
| Security Model |
Execute is granted to PUBLIC |
| 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 |
| |
| 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; |
| |
| DBMS_APPLICATION_INFO 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'; |
