Oracle Forms

 

A dynamic Report launcher

 

Home page

 

 

1.    Purpose

 

 

This is a tutorial and an Oracle Forms tool that show how, in a single dialog, to launch any report with 0 up to 10 parameters.

It needs 2 Oracle tables to store definition of the reports and their associated parameters.

 

Each parameter support the following properties:

 

q       Required

q       Datatype

q       Hint text

q       Default value

q       Format mask

q       LOV select order

q       Validation rule

 

Two dialogs are provided to run the demonstration

 

q       DYN_REP.FMB to launch the reports

q       DYN_REP_BUILD.FMB to manage report and parameters definition

 

 

2.    The Oracle tables

 

The table that store the reports properties

 

CREATE TABLE REP

(

  REP_NUM        NUMBER              NULL,

  REP_NAME       VARCHAR2(100 BYTE)  NOT NULL,

  REP_TITLE      VARCHAR2(256 BYTE)  NOT NULL,

  REP_SERVER     VARCHAR2(100 BYTE)  NULL,

  REP_DESTYPE    VARCHAR2(20 BYTE)   DEFAULT 'CACHE'          NOT NULL,

  REP_DESNAME    VARCHAR2(256 BYTE)  NULL,

  REP_DESFORMAT  VARCHAR2(20 BYTE)   DEFAULT 'PDF'            NOT NULL,

  REP_EXEC_MODE  VARCHAR2(30 BYTE)   DEFAULT 'ASYNCHRONOUS'   NOT NULL,

  REP_COMM_MODE  VARCHAR2(15 BYTE)   DEFAULT 'BATCH'          NOT NULL

)

 

The table that store the report parameters properties

 

CREATE TABLE REP_PARAM

(

  REP_NUM              NUMBER                NULL,

  REP_PAR_REP_NAME     VARCHAR2(30 BYTE)     NULL,

  REP_PAR_ORDER        NUMBER(2)             DEFAULT 1        NULL,

  REP_PAR_TITLE        VARCHAR2(256 BYTE)    NOT NULL,

  REP_PAR_TYPE         VARCHAR2(20 BYTE)     DEFAULT 'CHAR'   NOT NULL,

  REP_PAR_LOV          VARCHAR2(2000 BYTE)   NULL,

  REP_PAR_VALIDATION   VARCHAR2(512 BYTE)    NULL,

  REP_PAR_DEFAULT      VARCHAR2(256 BYTE)    NULL,

  REP_PAR_HINT         VARCHAR2(128 BYTE)    NULL,

  REP_PAR_ERRMSG       VARCHAR2(256 BYTE)    NULL,

  REP_PAR_CASE         VARCHAR2(1 BYTE)      DEFAULT 'M'      NOT NULL,

  REP_PAR_FORMAT_MASK  VARCHAR2(50 BYTE)     NULL,

  REP_PAR_REQUIRED     VARCHAR2(1 BYTE)      DEFAULT 'N'      NOT NULL

)

 

 

3.    The launcher dialog

 

 

The list item (Report title) allows to choose the report to launch and display dynamically

the associated parameters.

 

The form contains 10 CHAR items, 10 NUMBER items and 5 DATE items.

When you choose a specific report, the table that contain the parameter description is read,

and the corresponding items are showed on the screen.

 

This operation is performed in the INIT_PARAMS program unit called by the When-List-Changed trigger:

 

 

PROCEDURE Init_Params IS

  LC$Req Varchar2(512) ;

 

  Cursor C_PARAMS IS

  Select    *

  From      REP_PARAM

  Where     REP_NUM = :REP.REP_LIST

  Order by  REP_PAR_ORDER ;

 

  LR$Rec  C_PARAMS%ROWTYPE ;

  LN$I   Pls_Integer := 1 ;

BEGIN

 

 

  -- Hide all parameters --

  For i IN 1..10 Loop

       Set_Item_Property( 'PARAMS.L' || Ltrim( To_char( i,'00' ) ), VISIBLE, PROPERTY_FALSE ) ;

       Set_Item_Property( 'PARAMS.C' || Ltrim( To_char( i,'00' ) ), VISIBLE, PROPERTY_FALSE ) ;        

       Set_Item_Property( 'PARAMS.N' || Ltrim( To_char( i,'00' ) ), VISIBLE, PROPERTY_FALSE ) ;        

  End loop ;

  For i IN 1..5 Loop

       Set_Item_Property( 'PARAMS.D' || Ltrim( To_char( i,'00' ) ), VISIBLE, PROPERTY_FALSE ) ;

  End loop ;

 

  If :REP.REP_LIST IS NOT NULL Then

      

       -- Populate the internal collection with parameter properties --

       PKG_VARS.TB_Params.DELETE ;

       Open C_PARAMS ;

       Loop

             Fetch C_PARAMS Into LR$Rec ;

              Exit When C_PARAMS%NOTFOUND ;

             PKG_VARS.TB_Params(LN$I) := LR$Rec ;

             LN$I := LN$I + 1 ;

       End loop ;

       Close C_PARAMS ;

      

       If PKG_VARS.TB_Params.COUNT > 0 Then

          -- Show corresponding items --

          Display_Params ;

       End if ;

      

  End if ;

 

END;

 

PROCEDURE Display_Params IS

  LN$PosY   Pls_integer ;

  LC$Item   Varchar2(61) ;

  LC$First  Varchar2(61) ; 

  LC$Prec   Varchar2(61) ;

  LN$I      Pls_Integer ;

  LN$C      Pls_Integer := 1 ;

  LN$N      Pls_Integer := 1 ;

  LN$D      Pls_Integer := 1 ;

  LN$Height Pls_Integer := 0 ;

BEGIN

 

  LN$PosY := Get_Item_Property( 'PARAMS.L01', Y_POS ) ;

 

  -- Show parameters --

  LN$I := 1 ;

  For i IN PKG_VARS.TB_Params.First .. PKG_VARS.TB_Params.Last Loop

              

              LC$Item := 'L' || ltrim(to_char(LN$I,'00')) ;

              Copy( PKG_VARS.TB_Params(i).REP_PAR_TITLE, LC$Item ) ;

              Set_Item_Property( LC$Item , VISIBLE, PROPERTY_TRUE ) ;

              Set_Item_Property( LC$Item, Y_POS, LN$PosY ) ;

              

              If PKG_VARS.TB_Params(i).REP_PAR_TYPE = 'CHAR' Then

                     LC$Item := 'C' || ltrim(to_char( LN$C, '00' ) ) ;

                     LN$C := LN$C + 1 ;

              ElsIf PKG_VARS.TB_Params(i).REP_PAR_TYPE = 'NUMBER' Then  

                     LC$Item := 'N' || ltrim(to_char( LN$N, '00' ) ) ;

                     LN$N := LN$N + 1 ;                     

              Else

                     LC$Item := 'D' || ltrim(to_char( LN$D, '00' ) ) ;

                     LN$D := LN$D + 1 ;                     

              End if ;

              

              PKG_VARS.TB_Items(i) := LC$Item ;

              

              If LN$I = 1 Then

                      LC$First := 'PARAMS.' || LC$Item ;

              End if ;

              Set_Item_Property( LC$Item, VISIBLE, PROPERTY_TRUE ) ;

              Set_Item_Property( LC$Item, ENABLED, PROPERTY_TRUE ) ;

              Set_Item_Property( LC$Item, INSERT_ALLOWED, PROPERTY_TRUE ) ;

              Set_Item_Property( LC$Item, UPDATE_ALLOWED, PROPERTY_TRUE ) ;

              Set_Item_Property( LC$Item, Y_POS, LN$PosY ) ;

              -- Default value --

              If PKG_VARS.TB_Params(i).REP_PAR_DEFAULT IS NOT NULL Then

                      Copy( PKG_VARS.TB_Params(i).REP_PAR_DEFAULT, LC$Item ) ;

              End if ;

              -- Parameter name --

              Set_Item_Property( LC$Item, TOOLTIP_TEXT, PKG_VARS.TB_Params(i).REP_PAR_REP_NAME ) ;

              -- Hint text --

              Set_Item_Property( LC$Item, HINT_TEXT, PKG_VARS.TB_Params(i).REP_PAR_HINT ) ;

              -- Format mask --

              Set_Item_Property( LC$Item, FORMAT_MASK, PKG_VARS.TB_Params(i).REP_PAR_FORMAT_MASK ) ;

              -- Case restriction --

              If PKG_VARS.TB_Params(i).REP_PAR_CASE = 'U' Then

                     Set_Item_Property( LC$Item, CASE_RESTRICTION, UPPERCASE ) ;

              ElsIf PKG_VARS.TB_Params(i).REP_PAR_CASE = 'L' Then

                     Set_Item_Property( LC$Item, CASE_RESTRICTION, LOWERCASE ) ;

              End if ;

              -- Required ? --

              If PKG_VARS.TB_Params(i).REP_PAR_REQUIRED = 'Y' Then

                      Set_Item_Property( LC$Item, REQUIRED, PROPERTY_TRUE ) ;

              End if ;

              

              If LN$I > 1 Then

                      Set_Item_Property( LC$Prec, NEXT_NAVIGATION_ITEM, LC$Item ) ;

                      Set_Item_Property( LC$Item, PREVIOUS_NAVIGATION_ITEM, LC$Prec ) ;

              End if ;

              

              LC$Prec := LC$Item ;

              LN$I := LN$I + 1 ;

              LN$PosY := LN$PosY + Get_Item_Property( LC$Item, HEIGHT ) ;

                     

  End loop ;

 

  If LN$I > 1 Then

              Set_Item_Property( LC$Item,  NEXT_NAVIGATION_ITEM, LC$First ) ;

              Set_Item_Property( LC$First, PREVIOUS_NAVIGATION_ITEM, LC$Item ) ;      

  End if ;

 

  If LN$I > 1 Then

        Go_Item( LC$First ) ;

  Else

        Go_Block( 'REP' ) ;

  End if ;

 

END;

 

 

If a LOV select order is setted on a parameter, you can display the associated LOV

 

 

There are 5 LOVs defined in the module (for 1, 2, ..., 5 columns)

The value returned is allways the first column of the LOV, and the target item is :CTRL.RECEPT

 

This is the code that constructs the LOV, called in the When-New-Item-Instance trigger:

 

Display_Lov( :REP.REP_LIST, Get_Block_Property( 'PARAMS', CURRENT_RECORD )) ;

 

 

PROCEDURE Display_Lov

 (

   PN$Num   IN NUMBER,

   PN$Col   IN NUMBER

 ) IS

 

  LC$Select  Varchar2(2000) ;

  LC$Title   Varchar2(256) ;

  rg_name    Varchar2(20) := 'RG_GROUP' ;

  rg_id      RecordGroup ;

  err        Number ;

 

  c NUMBER;

  d NUMBER;

  col_cnt INTEGER;

  rec_tab dbms_sql.desc_tab2;

  col_num NUMBER;

 

  LC$Lov  Varchar2(10) ;

  LC$Col  Varchar2(100) ;

 

  LC$Item  Varchar2(61) := :System.Trigger_Item ;

  LN$Width Pls_Integer ;

  LN$TotWidth Pls_integer := 0 ;

      

BEGIN

 

  LC$Col := Get_Item_Property( LC$Item, TOOLTIP_TEXT ) ;

 

  -- Get The Select order --

  Begin

    Select   REP_PAR_LOV, REP_PAR_TITLE

    Into     LC$Select, LC$Title

    From     REP_PARAM

    Where    REP_NUM = PN$Num

    And      REP_PAR_REP_NAME = LC$Col

    ;

 

  Exception

       When NO_DATA_FOUND Then

          goto the_end ;

  End ;

 

  If LC$Select IS NULL Then

         goto the_end ;

  End if ;

 

  BEGIN

    c := dbms_sql.open_cursor;

    dbms_sql.parse(c, LC$Select, 1);

    d := dbms_sql.EXECUTE(c);

  EXCEPTION

       WHEN OTHERS THEN

         dbms_sql.close_cursor(c);

         Raise form_trigger_failure ;

  END ;

  dbms_sql.describe_columns2(c, col_cnt, rec_tab);

  dbms_sql.close_cursor(c);

   

  col_num := rec_tab.last ;

 

  -- LOV name --

  LC$Lov := 'LV' || ltrim( to_char( col_num ) ) || 'C' ;

 

  rg_id := Find_Group( rg_name ) ;

  If not ID_NULL( rg_id ) Then

        Delete_Group( rg_id ) ;

  End if ;

 

  -- Create and populate the record group --

  rg_id := Create_Group_From_Query( rg_name, LC$Select ) ;

  err := Populate_Group( rg_name ) ;

 

  -- Set the LOV column properties --

  For i In rec_tab.first .. rec_tab.last Loop

        -- Title --

        Set_Lov_Column_Property( LC$Lov, i, TITLE, rec_tab(i).col_name ) ;

        -- Width --

          IF rec_tab(i).col_type = 1 THEN

             LN$Width := rec_tab(i).col_max_len * 11 ;

          ELSIF rec_tab(i).col_type = 2 THEN

             LN$Width := rec_tab(i).col_precision * 11 ;

          ELSIF rec_tab(i).col_type = 12 THEN

             LN$Width := 80  ;

          END IF ;

          If LN$Width > 200 Then

                LN$Width := 200 ;

          End if ;

          Set_Lov_Column_Property( LC$Lov, i, WIDTH, LN$Width ) ;

          LN$TotWidth := LN$TotWidth + LN$Width + 20 ;

  End loop ;

  -- LOV properties --

  Set_Lov_Property( LC$Lov, LOV_SIZE, LN$TotWidth, 400 ) ;

  Set_Lov_Property( LC$Lov, TITLE, LC$Title ) ;

  Set_Lov_Property( LC$Lov, GROUP_NAME, rg_name ) ;

 

  Set_Item_Property( LC$Item, LOV_NAME, LC$Lov ) ;

 

<<the_end>>

null ;

 

END;

 

 

4.    The builder dialog

 

 

It allows to set the reports and parameters properties.

 

q       Enter in the first block the basic report specifications

 

q       Enter in the second block the parameters specifications

 

Name is the name of the parameter defined in the report module

Req allows to set the parameter required or not

Title is the parameter label

 

 

Datatype can be CHAR, NUMBER or DATE.

 

Case allows to set the restriction case of the parameter.

Allowed values are:

 

q       (U)ppercase

q       (L)owercase

q       (M)ixt

 

LOV order allows to define the SELECT order for the lov attached on the parameter.

You can define SELECT order with 1 up to 5 columns.

Each column must have an alias (COL1 to COL5)

 

 

 

Validation rule allows to set a valid SQL to validate the parameter.

 

 

 

Error message allows to set an error message displayed to the end user when the validation failed.

 

 

You can also define a format mask for the parameter.

 

 

5.    The sample dialogs

 

 

Ø      Download the dynrep.zip file

 

Ø      Unzip the dynrep.zip file

 

Ø      Under Sql*Plus, run the /scripts/install.sql to create the following tables:

 

1.      EMP

2.      DEPT

3.      REP

4.      REP_PARAM

 

 

Ø      Open the /reports/EMP_LIST.rdf and /reports/DEPT_LIST.rdf modules (Oracle Reports 9.0.2)

 

Ø      Open the /forms/DYN_REP.FMB and /forms/DYN_REP_BUILD.FMB modules (Oracle Forms 9.0.2)

 

Ø      Compile all and run the modules