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
This comment has been removed by the author.
ReplyDeleteWhy not use "FROM clause query" ?
ReplyDeleteYou can do it using "FROM clause query" but you will lose advantage of data block based on table
DeleteThis is what cluster tables are used for
ReplyDeleteiam not able to insert data into this form
ReplyDeleteerror
ORA-00926: missing VALUES keyword
Did you download this form ?
Deletehttp://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.
The same problem
Deletewhen I insert a new record
ORA-00926: missing VALUES keyword
please, help
I know this is an old topic, but I had the same problem and there was no solution here. I fixed it by changing
DeleteDML 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'
give a sample .fmb for reports.......How to print the reports......plzz
ReplyDeleteCould you explain more about your request?
DeleteMany 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===================================
Respect and that i have a nifty offer you: What To Expect When Renovating A House home renovation youtube
ReplyDelete