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