Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Troubles with dbms_application_info.set_session_longops
In article <332bb004.0206140611.1cc7a9b8_at_posting.google.com>,
marcel.kraupp_at_gmx.ch says...
>
>Hello
>
> dbms_application_info.set_session_longops doesn't behave how I expect
> it or how I interprete the documentation.
>
> Here's the code I have picked up somewhere:
>
>
>
>create or replace procedure long_running_procedure
>as
> l_rindex pls_integer;
> l_slno pls_integer;
>
>begin
> dbms_application_info.set_client_info( 'This is me' );
> dbms_application_info.set_module( 'long_running_procedure', 'starting' );
>
>
> for i in 1 .. 30 loop
>
> l_rindex := dbms_application_info.set_session_longops_nohint;
> l_slno := NULL;
> dbms_application_info.set_session_longops
> ( rindex => l_rindex,
> slno => l_slno,
> op_name => 'LONG_RUNNNIG_PROCEDURE' || i,
> target => 1000000+i,
> context => 2000000+i,
> sofar => i,
> totalwork => 100,
> target_desc => 'What I''m Working On',
> units => 'numbers 1-100' );
>
> dbms_lock.sleep(1);
> end loop;
>end;
>/
>
It is due to the improper usage of the rindex and slno parameters. Here is a short extract from my book in this parameter:
....
o RINDEX: tells the server which row to modify in the V$SESSION_LONGOPS view. If
you set this value to dbms_application_info.set_session_longops_nohint, a new
row will be allocated in this view for you and the index of that row will be
returned in RINDEX. Subsequent calls to SET_SESSION_LONGOPS with the same value
for rindex will update that already existing row.
o SLNO: is an internal value. You should initially pass a null number in and ignore its value otherwise. You should pass the same value in with each call. ........
so, that assignment:
> for i in 1 .. 30 loop
>
> l_rindex := dbms_application_info.set_session_longops_nohint;
> l_slno := NULL;
should be:
> l_rindex := dbms_application_info.set_session_longops_nohint;
> l_slno := NULL;
> for i in 1 .. 30 loop
>
Here is a "proper" example from the book:
declare
l_nohint number default dbms_application_info.set_session_longops_nohint; l_rindex number default l_nohint; l_slno number;
for i in 1 .. 25
loop
dbms_lock.sleep(2); dbms_application_info.set_session_longops ( rindex => l_rindex, slno => l_slno, op_name => 'my long running operation', target => 1234, target_desc => '1234 is my target', context => 0, sofar => i, totalwork => 25, units => 'loops' );
end loop;
end;
/
>
>
>
> When I start the procedure and query v$session_longops, oracle
> shows me an entry for each call that was made to set_session_longops (30
> in the end). However, I expected there to be one row only.
>
> Is this how it is supposed to work or is something wrong with the code?
>
> Also, how can I get rid of the rows in v$session_longops?
>
>
> MK
-- Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Fri Jun 14 2002 - 13:55:48 CDT