Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Troubles with dbms_application_info.set_session_longops

Re: Troubles with dbms_application_info.set_session_longops

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 14 Jun 2002 11:55:48 -0700
Message-ID: <aede7k0old@drn.newsguy.com>


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;

begin

    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 Corp 
Received on Fri Jun 14 2002 - 13:55:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US