Overblog Suivre ce blog
Editer l'article Administration Créer mon blog

Search

Free tool

Look and Feel Project

25 octobre 2006 3 25 /10 /octobre /2006 15:59
Here is a workaround that allows to count rows impacted by DML operations in each based block of a form.

It consists in three form-level triggers and one program unit.

The program unit:

PROCEDURE Dml_rowcount ( PC$Mode In Varchar2 ) IS
   LC$Var  Varchar2(100) ;
   LN$Cpt  pls_integer ;
Begin
   -- summarize for block detail --
   LC$Var := 'GLOBAL.' || :system.trigger_block || PC$Mode;
   Default_Value('0',LC$Var);   
   LN$Cpt := Nvl(Name_In(LC$Var),0) + 1 ;
   Copy( To_Char(LN$Cpt), LC$Var ) ;
   -- summarize for all blocks --
   LC$Var := 'GLOBAL.' || PC$Mode;
   Default_Value('0',LC$Var);   
   LN$Cpt := Nvl(Name_In(LC$Var),0) + 1 ;
   Copy( To_Char(LN$Cpt), LC$Var ) ;   
End ;

It receive as the only one argument the mode wich can be one of the following

  • INS  for insert
  • UPD for update
  • DEL for delete

This procedure is called from the 3 form-level triggers:


POST-INSERT trigger:

   Dml_rowcount( 'INS' ) ;


POST-UPDATE trigger:

   Dml_rowcount( 'UPD' ) ;

Post-DELETE trigger:

   Dml_rowcount( 'DEL' ) ;


In a Key-Commit trigger, you can, then, display the result for a particular block of for all the blocks of the form with the following function:

FUNCTION Get_Rowcount ( PC$BlkName In Varchar2 Default NULL) RETURN Varchar2 IS
  LC$Result  Varchar2(100) ;
  LC$Var1    Varchar2(80) ;
  LC$Var2    Varchar2(80) ;
  LC$Var3    Varchar2(80) ;
BEGIN
  If PC$BlkName is not null Then
     LC$Var1 := 'GLOBAL.' || PC$BlkName || 'INS' ;
     LC$Var2 := 'GLOBAL.' || PC$BlkName || 'UPD' ;                 
     LC$Var3 := 'GLOBAL.' || PC$BlkName || 'DEL' ;                                   
     Default_Value('0',LC$Var1);
     Default_Value('0',LC$Var2);     
     Default_Value('0',LC$Var3);
  End if ;
  Default_Value('0','GLOBAL.INS');
  Default_Value('0','GLOBAL.UPD');
  Default_Value('0','GLOBAL.DEL');       
  If PC$BlkName is not null Then
    LC$Result := 'Ins='  || Name_In( LC$Var1 ) ||
                 '   Upd=' || Name_In( LC$Var2 ) ||
                 '   Del=' || Name_In( LC$Var3 ) ; 
    Copy( 0, LC$Var1 ) ;
    Copy( 0, LC$Var2 ) ;
    Copy( 0, LC$Var3 ) ;
  Else
    LC$Result := 'Ins='  || :GLOBAL.INS || '   Upd=' || :GLOBAL.UPD|| '   Del=' || :GLOBAL.DEL ;     
    Copy( 0, 'GLOBAL.INS' ) ;
    Copy( 0, 'GLOBAL.UPD' ) ;
    Copy( 0, 'GLOBAL.DEL' ) ;
  End if ;

 
  Return LC$Result ;
 
END;


Exemple of Key-Commit, On-Commit or Post-Database-Commit trigger:

Commit_Form ;
-- Count for the EMP block --
message( Get_Rowcount('EMP') ) ;
-- Count for all the form --
message( Get_Rowcount() ) ;

Both Dml_Rowcount() and Get_Rowcount() can be stored in a pl/sql library.

Partager cet article

Repost 0
Francois Degrelle - dans Oracle Forms
commenter cet article

commentaires