v$session_longops in 10gR2

I have read an excellent overview of DBMS_APPLICATION_INFO in Andy Campbell blog on If only…

I have written a procedure P which is executing 5 statements. I want to add the name of the procedure P as a target. I do not want to hardcode the name however… So I can use $$PLSQL_UNIT in 10gR2 (documented in Using Predefined Inquiry Directives With Conditional Compilation)

Ok, here I go

create or replace procedure p is
rindex binary_integer;
slno binary_integer;
sofar number;
totalwork number;
target number;
begin
totalwork := 5;
sofar := 0;
select object_id
into target
from user_objects
where object_name = $$PLSQL_UNIT;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
end;
/

Now I execute it

SQL> exec p

PL/SQL procedure successfully completed.

and monitor it

SQL> select sid,opname,target,sofar,totalwork,units
2 from v$session_longops;
SID OPNAME TARGET SOFAR TOTALWORK UNITS
---- ------------ -------- ----- --------- ---------
538 Executing... SCOTT.P 5 5 Statement

3 thoughts on “v$session_longops in 10gR2

  1. Laurent Schneider Post author

    I noticed that op_name, target, target_description and units cannot be changed, so now I do, before starting

    DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
    rindex=>rindex,
    slno=>slno,
    op_name=>’execute…’,
    target=>target,
    units=>’statements’);

    and later

    DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
    rindex=>rindex,
    slno=>slno,
    sofar=>sofar,
    totalwork=>totalwork)

    a little bit less to type 🙂

  2. Ben

    Can this be used with Perl scripting? I’ve used set_session_info in perl but I’m wondering how to use set_session_longops in a code loop other than PL/SQL.

Comments are closed.