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.


Conclusion
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

Thanks
Mahmoud A. El-Sayed

Popular posts from this blog

ADF : Get Current Logged User Name

OAF : Get Current Row in Table

ADF : Working with ViewCriteria