23 April, 2012

Discrimination between Odd and Even Records in Oracle Forms

In tabular block in Oracle Forms, I want to discriminate between odd records with different color from color of even records alternately so the block in run time look like below image.


The idea of this post is based on my previous post Highlighting Selected Records in Oracle Forms as I use visual attribute for every navigable items in block.



Let's state step by step to do this task on tabular block based on SCOTT.EMP table

1-Create Tabular Block Based on SCOTT.EMP Table

2- Create two Visual Attribute
ODD visual attribute
Create visual attribute and name it ODD and change its properties as below
Background Color : gray16

Even Visual Attribute
Create another visual attribute and name it EVEN and change its property as below.
Background Color : gray4

3-Create Stored procedure name it SET_ODD_EVEN_VA
PROCEDURE SET_ODD_EVEN_VA
IS
   LC$CURR_BLOCK   VARCHAR2 (80) := :SYSTEM.TRIGGER_BLOCK;
   LC$CURR_ITEM    VARCHAR2 (80);
   LN$CURR_REC     NUMBER;
BEGIN
   LC$CURR_ITEM := GET_BLOCK_PROPERTY (LC$CURR_BLOCK, FIRST_ITEM);
   LN$CURR_REC := GET_BLOCK_PROPERTY (LC$CURR_BLOCK, CURRENT_RECORD);

   WHILE (LC$CURR_ITEM IS NOT NULL)
   LOOP
      LC$CURR_ITEM := LC$CURR_BLOCK || '.' || LC$CURR_ITEM;
      DISPLAY_ITEM (LC$CURR_ITEM,
                    CASE (LN$CURR_REC MOD 2)
                       WHEN 0
                          THEN 'ODD'
                       ELSE 'EVEN'
                    END
                   );
      LC$CURR_ITEM := GET_ITEM_PROPERTY (LC$CURR_ITEM, NEXT_NAVIGATION_ITEM);
   END LOOP;
END;

4- Cal  SET_ODD_EVEN_VA
You should call SET_ODD_EVEN_VA procedures in below triggers at block level
WHEN-NEW-RECORD-INSTANCE
POST-QUERY

5- Write in WHEN-NEW-FORM-INSTANCE trigger at form level the below code
 GO_BLOCK('EMP');  
 EXECUTE_QUERY;  

6-Run the form
The form will open and display all records in SCOTT.EMP table and will display tabular block like below image

You can download sample Form from here

Thanks
Mahmoud A. El-Sayed

8 comments:

  1. Good post for doing highlighting dynamically without defining static item names

    ReplyDelete
  2. Can you share an error you faced when compile form?

    ReplyDelete
  3. nice work...
    i want to write something on how we can buit a oracle report base on excel sheet rather than db table

    ReplyDelete
  4. Gmd Case when support in sql not plsql you can modify that as:
    PROCEDURE SET_ODD_EVEN_VA IS
    LC$CURR_BLOCK VARCHAR2 (80) := :SYSTEM.TRIGGER_BLOCK;
    LC$CURR_ITEM VARCHAR2 (80);
    LN$CURR_REC NUMBER;
    BEGIN
    LC$CURR_ITEM := GET_BLOCK_PROPERTY (LC$CURR_BLOCK, FIRST_ITEM);
    LN$CURR_REC := GET_BLOCK_PROPERTY (LC$CURR_BLOCK, CURRENT_RECORD);

    WHILE (LC$CURR_ITEM IS NOT NULL)
    LOOP
    LC$CURR_ITEM := LC$CURR_BLOCK || '.' || LC$CURR_ITEM;
    if (LN$CURR_REC MOD 2) =0 THEN
    DISPLAY_ITEM (LC$CURR_ITEM,'ODD');
    ELSE
    DISPLAY_ITEM (LC$CURR_ITEM,'EVEN');
    END IF;
    LC$CURR_ITEM := GET_ITEM_PROPERTY (LC$CURR_ITEM, NEXT_NAVIGATION_ITEM);
    END LOOP;
    END;

    ReplyDelete
  5. Nice Idea !!!, Which is very Efficient Looking of my page....
    Jajakallah..

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...