11 June, 2012

Oracle Forms : Data Block Based on multiple Table

As usual, data block in oracle forms is based on single table.
Today I will present a case to create single data block based on multiple tables.

I will implement solution on SCOTT schema.
I will display one block based on two tables(EMP and DEPT)

1- Create single data block using data block wizard based on EMP table
2- Create Layout wizard of EMP block and create it as tabular form like below image

3- For every database item in EMP block add prefix "EMP." to Column Name property
for example EMP.EMPNO, EMP.ENAME, EMP.JOB ,............ ie.

4- I will change properties of block to get data from SCOTT.EMP, SCOTT.DEPT and add Join condition between two tables

Change the below Properties of EMP block
    Query Data Source Name :  SCOTT.EMP,SCOTT.DEPT
    DML Data Target Name   :  SCOTT.EMP,SCOTT.DEPT
    Locking Mode                    :  Delayed
    Key Mode                          :  Updatable
    Where Clause                     :  EMP.DEPTNO = DEPT.DEPTNO

5- I will add some items in EMP block based on DEPT table
Add the below two Items  
      1- Name : DNAME
          Data Type :  Char
          Data Length Semantics:  Byte
          Maximum Length :  15
          Column Name :  DEPT.DNAME
          Prompt Name :  Department Name
          Prompt Attachment Edge :  Top
          Prompt Alignment :  Center

      2- Name : LOC
          Data Type :  Char
          Data Length Semantics :  Byte
          Maximum Length :  14
          Column Name :  DEPT.LOC
          Prompt Name :  Location
          Prompt Attachment Edge :  Top
          Prompt Alignment :  Center

The layout should be like below image

6- Now I finished designing the form. Let's run it and see the data at run time.

You can create Data Block based on more than two tables and you can join them in Where Clause property of the block.
Display the data at block based on join is faster than  single block and get joined data in POST-QUERY trigger.
You can download sample form here

Mahmoud A. El-Sayed

OAF :Formatting DateTime Fields

Use the following code to format DateTime feilds in OAF OAWebBean departureDateBean = webBean.findChildRecursive("DepartureDate"...