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.


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

11 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Why not use "FROM clause query" ?

    ReplyDelete
    Replies
    1. You can do it using "FROM clause query" but you will lose advantage of data block based on table

      Delete
  3. This is what cluster tables are used for

    ReplyDelete
  4. iam not able to insert data into this form
    error

    ORA-00926: missing VALUES keyword

    ReplyDelete
    Replies
    1. Did you download this form ?
      http://mahmoudadfexamples.googlecode.com/files/MULTIPLE_TABLES.fmb

      It is working well.
      Assure that you did steps in #4 point and table name must be prefixed with schema name.

      Delete
    2. The same problem
      when I insert a new record
      ORA-00926: missing VALUES keyword

      please, help

      Delete
    3. I know this is an old topic, but I had the same problem and there was no solution here. I fixed it by changing

      DML Data Target Name : SCOTT.EMP,SCOTT.DEPT to
      DML Data Target Name : SCOTT.EMP

      In other words, I only included the original table that my block was based upon. Also, for any fields that you don't want to be part of the insert statement, change the query only to 'Y' and (insert/update) to 'N'

      Delete
  5. give a sample .fmb for reports.......How to print the reports......plzz

    ReplyDelete
  6. Many organizations using older versions of Oracle Forms are overlooking more functionality, better convenience and lower maintenance costs. With more organizations favoring web and service-oriented architecture, lone Oracle Forms applications can seriously hamper innovation and efficiency. Thanks! ~ Charlotte W. from astatios
    ===================================

    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...