Forms : a progress bar to follow stored procedure execution

Here is a way that allows following the progression of a stored procedure.



The following code is used to create a sample stored procedure to test.
It uses the dbms_application_info.set_session_longops to insert "tags" that can be read by the Forms application.

Create or replace procedure Progress_Bar
As
   rindex pls_integer := -1;
   slno   pls_integer;
Begin
  -----------------------------------------------------
  --  procedure that does nothing else than waiting  --
  --  to illustrate the ProgressBar Forms sample     --
  -----------------------------------------------------
   dbms_application_info.set_session_longops(
     RINDEX      => rindex
    ,SLNO        => slno
    ,OP_NAME     => 'PROGRESS_BAR'
    ,SOFAR       => 0
    ,TOTALWORK   => 100
   );
   --  simulating the task progress  --
   For i IN 1..100 loop
     DBMS_LOCK.SLEEP(.3);
     dbms_application_info.set_session_longops(
       RINDEX      => rindex
      ,SLNO        => slno
      ,OP_NAME     => 'PROGRESS_BAR'
      ,SOFAR       => i
      ,TOTALWORK   => 100
     );
   End loop;
End;

Then the following Forms PL/SQL code (both 9i and 10g database version are provided)

Depending on the database version you have, paste the corresponding code in the When-Button-Pressed trigger of the sample dialog shipped.

------------------------
--  Database 10g code --
------------------------
Declare
  v_jobname  Varchar2(30) := 'PROGRESS_BAR_JOB' ;
  v_jobid    Number := 12345 ;
  v_percent  Number :=  0 ;
  v_end      Exception ;
  v_nb       Pls_integer ;
  v_pass     Pls_integer := 0 ;   
begin
  -- we start the stored procedure through the DBMS_SCHEDULER.CREATE_JOB function --
  dbms_scheduler.create_job(
    job_name   => v_jobname
   ,job_type   => 'stored_procedure'
   ,job_action => 'Progress_Bar'
   ,start_date => SYSDATE
   ,enabled    => TRUE
  );
 
  -- job's running? --
  Loop
     Select count(*)
     Into   v_nb
     From   USER_SCHEDULER_JOBS
     Where  JOB_NAME = v_jobname;
    
     v_pass := v_pass + 1 ;
     If v_pass > 100 Then
           -- le job ne se lance pas --
           message('Job not running',acknowledge);
           Raise Form_Trigger_Failure ;
     End if ;
     exit when v_nb > 0  ;
     dbms_lock.sleep(.2);
  End loop ;
 
  set_item_property('blo_progress.progressbar', width, 0);
  v_percent :=0;
 
  -- following the procedure's execution --
  Loop   
     
      Exit when v_percent >= 100;
      Select  (sofar / totalwork) * 100
      Into    v_percent
      From    v$session_longops
      Where   opname = 'PROGRESS_BAR' and sofar < totalwork;
          
      :blo_progress.progressbar:= v_percent||'%';
      set_item_property('blo_progress.progressbar',width, round( v_percent*2,2));
      synchronize;
    
  End loop;

  raise v_end ;
 
Exception
   When NO_DATA_FOUND then
      set_item_property('blo_progress.progressbar', width, 200);
      :blo_progress.progressbar:= '100%';
      raise v_end ;
   When TOO_MANY_ROWS then
      raise v_end ;
   When v_end then
      DBMS_SCHEDULER.drop_job (job_name => v_jobname,FORCE=> true) ;  
   When Others then
      DBMS_SCHEDULER.drop_job (job_name => v_jobname,FORCE=> true) ;  
End;


-----------------------
--  Database 9i code --
-----------------------
Declare
  v_jobname  Varchar2(30) := 'PROGRESS_BAR_JOB' ;
  v_jobid    Number := 12345 ;
  v_percent  Number :=  0 ;
  v_end      Exception ;
  v_nb       Pls_integer ;
  v_pass     Pls_integer := 0 ;   
begin
  -- we start the stored procedure through the DBMS_JOB.ISUBMIT function --
  dbms_job.isubmit(v_jobid,'Progress_Bar;',sysdate,null);
  forms_ddl('commit') ;
  dbms_job.run(v_jobid);
 
  -- job's running? --
  Loop
    Select count(job)
    Into   v_nb
    From   user_jobs
    Where  job= v_jobid
    And    total_time!=0;
    
     v_pass := v_pass + 1 ;
     If v_pass > 100 Then
           -- le job ne se lance pas --
           message('Job not running',acknowledge);
           Raise Form_Trigger_Failure ;
     End if ;
     exit when v_nb > 0  ;
     dbms_lock.sleep(.2);
  End loop ;
 
  set_item_property('blo_progress.progressbar', width, 0);
  v_percent :=0;
 
  -- following the procedure's execution --
  Loop   
     
      Exit when v_percent >= 100;
      Select  (sofar / totalwork) * 100
      Into    v_percent
      From    v$session_longops
      Where   opname = 'PROGRESS_BAR' and sofar < totalwork;
          
      :blo_progress.progressbar:= v_percent||'%';
      set_item_property('blo_progress.progressbar',width, round( v_percent*2,2));
      synchronize;
    
  End loop;

  raise v_end ;
 
Exception
   When NO_DATA_FOUND then
      set_item_property('blo_progress.progressbar', width, 200);
      :blo_progress.progressbar:= '100%';
      raise v_end ;
   When TOO_MANY_ROWS then
      raise v_end ;
   When v_end Then
   Delete From USER_JOBS Where JOB = v_jobid ;
   Forms_ddl('commit');
End;

Read the initial article (French)

The initial idea that consist to use the dbms_application_info to follow the procedure's execution comes from the following German article

Comments

[Tiep]

Dear sir,
Can you send me the source files for the forms and related files, please!
Because the initial article is in a different language than English and it requires registration, I can't down load them.
My email is: tiepmr@gmail.com
Thank you so much!

[Francois]

Get it from there: fdtool.free.fr/files/progressbar.zip

[Maciej Myrcha]

Hi Francois,


no, no, I haven't completely changed the stuff, but instead of using a job (which start the DB progress bar procedure), I use the java bean to start it. This procedure uses set_session_longpops
(as in your example), to set proper values of sofar and totalwork. I can see that 'sofar' changes properly, when I select v$session_longpops under pl/sql developer. The problem is that when I try
to select this in the mask (in the loop as in your example), I receive Numeric or values error after a while :(

[Francois Degrelle]

This kind of error mostly comes when you use an under-dimensioned variable

[Maciej Myrcha]

Hi Francois,


I'm trying to get this example to work in my case - instead of a job, I'm using a jaba bean to start the procedure asynchronously. And everything works fine until one moment - in the loop when I
check sofar and totalwork (in the mask) I suddenly got 'Numeric or value error' exception and I have no idea why :(


regards


Maciej

[Francois Degrelle]

so, you completly changed the stuff, then you talk about "sofar" and "totalwork", and without giving more detail about what you did differently, you hope I can help you ? Sorry, I have no crystal
ball.

[Stephane Vinette]

Forms : a progress bar to follow stored
procedure execution


 


Salut Francois,


Pour l'article #10986844, est-ce que l'on doit seulement utiliser Forms 10g, ou bien est-ce que cette procedure devrait fontionner avec Forms 6i.  J'ai essayer par tout les moyens de
l'executer avec Forms6i mais j'ai un probleme avec v$session_longops.  Le "query" ne voit pas le dernier job (le job qui execute)...


Tiens moi au courant!


Merci,


Steph

[Francois Degrelle]

Je ne pense pas que la version de Forms ait un impact dans la mesure ou la logique se situe dans la BDD, mais je ne saurais être catégorique dans la mesure ou cela fait bien longtemps que je n'ai
plus de version 6 de Forms sous le coude!