Relations in Forms are by far one of the best methods of creating and preserving data relationships between database tables, thereby maintaining data integrity. This concept of relations in forms has been extended to accommodate object tables and relational tables having object columns (henceforward referred to as object-relational tables).
A master-detail relation can be created to link:
A master-detail relation linking any of the preceding can be created by
Using Join Conditions for Forms Relations
This technique is similar to joining two relational tables having a primary-key (PK) and foreign-key (FK) relationship between them. Using join conditions for Forms relations is still reserved to preserve such PK-FK foreign key relationships.
The join condition for our ADDRESS_PERSON relation would look like
address.street = person.off_add_street AND address.city = person.off_add_city AND address.state = person.off_add_state AND address.zip = person.off_add_zip
Using REF Items for Forms Relations
This subsection explains how to create a master-detail relation between blocks using REF items. First we tell what a REF item is and then explain how to create a relation using it.
The object table ADDRESS and the object-relational table PERSON ”even though not " related " by means of a foreign key at the server end ”are linked by a REF reference, and can be linked in forms using a master-detail relationship.
REF items provide a means of linking object and/or object-relational tables. This means the query and DML work behind the scenes as they do for a relation involving two relational tables.
You should always create a relation based on REFS while linking object and/or object relational tables.
A REF reference is required between two objects and/or object-relational tables for the following reasons:
In the example form,
Select the REF column(s) while creating the detail block. All the REF columns have to be selected.
Figure 10.1 shows a sample screen while creating the PERSON table that has two REF columns OFF_ADD and HOME_ADD.
Figure 10.1. Creation of a Data Block with REF items.
The column OFF_ADD (highlighted in the figure) is the actual REF column from the PERSON table. This column holds a pointer to a row in the ADDRESS object table and Forms generates a unique Object Identifier for it that acts as an Object FK to the ADDRESS table.
You can see that the OFF_ADD is named OFF_ADD (Lookup). This is look up related and is generated automatically by Forms to populate an LOV for the OFF_ADD item.
The relation can be created in two ways. First, you can create the relation when you create the PERSON block. This is done by clicking the Create Relationships button and checking the Auto-join Data Blocks check box. Forms prompts for a choice of master blocks if there are multiple REF items in the PERSON block. This is shown in Figure 10.2.
Figure 10.2. List of master blocks in case of multiple REF items.
The relation is created with the following special properties: Relation Type is set to Ref, and Detail Reference Item is set to the Ref item corresponding to the REF column in the base object table (in this case, OFF_ADD ).
The following properties are set for the master and detail blocks:
Second, you can explicitly create a relation using the Relations node of the Object Navigator. The preceding properties for a relation have to be set in this case.
Always create one relation for each REF item included in the detail block.
The Master block should have the DML Returning Value property set to Yes. This has to be set to propagate the new OID generated each time a DML operation is performed on the same record ”for example, an UPDATE operation. This OID is the referred to OID: referred to by the detail block.
If the master block is an object table, the property Include REF item should be set to Yes.
References to an object are made by using the REF operator with the table alias passed as an input parameter. The row of the child object table is located by comparing this OID, returned by REF, to the REF column in the child table. ROWID rather than a primary key value is used to locate the correct OID corresponding to the block record.
This is shown as follows :
........ ........ CURSOR person_cur IS SELECT 1 FROM person p WHERE p.off_add = (SELECT REF(a) FROM address a WHERE rowid = :address.rowid); ........ ........
Advanced GUI Development: Developing Beyond GUI
Advanced Forms Programming
Object-oriented Methods in Forms
Intelligence in Forms
Additional Interesting Techniques
Working with Trees
Oracle 8 and 8i Features in Forms Developer