Record Groups and LOVs Based on Objects

This section presents the use of LOVs and Record Groups based on object tables and object columns . The procedure for dynamically creating such record groups is also highlighted.

Record groups and LOVs based on objects behave much in the same way as when based on tables.

Record groups can be on an object REF column, an object column, or an object type-based object table.

To create record groups based on Object Refs, select the columns from the corresponding object table or the object column corresponding to the object ref column from the table as the record group query.

The following query selects the individual columns from the object table ADDRESS. The OFF_ADD REF column in the PERSON table is based on the object type ADD_TYPE on which the object table ADDRESS is based:

SELECT REF(a), a.street, a.city, a.state, a.zip FROM address a

Do not select an object ref column by de-referencing it. For example, the following query is incorrect:

SELECT DEREF(a.off_add) FROM person a

Tip

Do not directly select the REF column because it displays the OID at runtime, which is like Greek or Latin to the user .

 

We will create an LOV based on the preceding SELECT and attach it to the OFF_ADD item (the REF item) in the PERSON block. This is to enable ad hoc querying in the PERSON block to enable the user to specify runtime query criteria in Enter-Query mode. This is shown in Figure 10.3.

Figure 10.3. An LOV SELECT statement involving a REF column.

The Record Group column specifications appear as shown in Figure 10.4.

Figure 10.4. Record Group Column Specifications for a REF item.

Note that the data type of column OID is OBJECT REF and that of the other columns is similar to the columns selected from a purely relational table. The length of OID is 0 to hide it in the LOV.

The LOV Column mapping appears as shown in Figure 10.5.

Figure 10.5. LOV Column Specifications for a REF item.

At runtime, the LOV displays the following data, as shown in Figure 10.6.

Figure 10.6. Addresses as they appear in the LOV.

The Effect of Directly Selecting the REF Column

Directly selecting the object REF column, as shown in Figure 10.7, will result in the Record Group Column Specification, LOV Column mapping, and a runtime LOV as shown in Figures 10.8, 10.9, and 10.10.

Figure 10.7. An LOV SELECT statement directly selecting only a REF column.

The Record Group Column specification appears as shown in Figure 10.8.

Figure 10.8. Record Group Specifications for an LOV based only on a single REF column.

Notice the data type of OBJECT REF.

The LOV column mapping properties appear as shown in Figure 10.9.

Figure 10.9. LOV Column Specifications for an LOV based only on a single REF column.

The preceding LOV ”when attached to the OFF_ADD REF item in the PERSON block ” displays the data shown in Figure 10.10 at runtime.

Figure 10.10. Figure showing an LOV displaying object REF OIDs.

Record Groups Based on Object Columns and/or Object-type Based Object Tables

The record group discussed earlier is also an example of a record group corresponding to an object-type based object table. Record groups based on object columns behave in the same manner as preceding and the corresponding query is similar. For example, consider the FOREIGN_ ADDRESS table. It has an object column FOR_ADD of object type ADD_TYPE that is specific to the country represented by the first column COUNTRY_CODE. The following query can be used to construct a record group that can then be used for an LOV for the FOR_ADD object column:

SELECT for_add.street, for_add.city, for_add.state, for_add.zip

FROM foreign_address

Here FOR_ADD is deliberately not made as a REF column for illustration purposes.

Again, this LOV is useful for specifying query criteria. For example, the user can query for all foreign addresses belonging to a particular COUNTRY_CODE and city.

Dynamically Creating Object Record Groups

Dynamically creating object record groups amounts to dynamically generating a query for the record group based on object refs or object columns. Even non-query record groups can be created and populated dynamically with each column corresponding to the object type, which is pointed to by the OBJECT REF or object column.

Tip

Columns of OBJECT REF data type are not supported in Forms. The individual components of an object column or an object table pointed to by the REF column without the OID have to be selected.

 

Listing 10.2 illustrates a typical example.

Listing 10.2 Procedure illustrating the use of Object REFs in Record Groups

PROCEDURE p_rg IS

 rg_id RECORDGROUP;

 gc_id1 GROUPCOLUMN;

 gc_id2 GROUPCOLUMN;

 gc_id3 GROUPCOLUMN;

 gc_id4 GROUPCOLUMN;

 gc_id5 GROUPCOLUMN;

 ret_id NUMBER;

BEGIN



/* Create the internal id of the record group */



 rg_id := CREATE_GROUP('RG_OBJ');



/* Add columns to the record group. Here the object id or OID column is added

/ as a LONG column */



 gc_id1 := ADD_GROUP_COLUMN(rg_id, 'OID',LONG_COLUMN, 128);

 gc_id2 := ADD_GROUP_COLUMN(rg_id, 'STREET', CHAR_COLUMN, 20);

 gc_id3 := ADD_GROUP_COLUMN(rg_id, 'CITY', CHAR_COLUMN, 20);

 gc_id4 := ADD_GROUP_COLUMN(rg_id, 'STATE', CHAR_COLUMN, 2);

 gc_id5 := ADD_GROUP_COLUMN(rg_id, 'ZIP', CHAR_COLUMN, 13);



/* Populate the record group with a SELECT statement. Note how the conversion

/functions, first REFTOHEX and then HEXTORAW are used to select the OID from

/the object table address */



 ret_id := POPULATE_GROUP_WITH_QUERY(rg_id,

 'SELECT HEXTORAW(REFTOHEX(REF(a))) oid, a.street street, a.city city, a.state state,

a.zip zip FROM address a');

 IF ret_id <> 0 THEN

 MESSAGE(DBMS_ERROR_TEXT, ACKNOWLEDGE);

 RAISE FORM_TRIGGER_FAILURE;END IF;



/* Assign the record group so created to the LOV under consideration */



 SET_LOV_PROPERTY('LOV_TEST', GROUP_NAME, rg_id);

END;

Here LOV_TEST should have five columns already defined.

The OID column is defined as a LONG column in the record group and the object id REF(a) is first converted to HEX and then to RAW using REFTOHEX and HEXTORAW, respectively.

The Return Item for OID is specified as the REF item in the block. The data type of this REF item in the block remains as OBJECT REF. In this case, it is OFF_ADD.

The remaining four columns are defined with the same definitions as the STREET, CITY, STATE, and ZIP columns in the object table ADDRESS.

Following the previous technique and using the LOV to select the OIDS of address, the DML operations on the PERSON table work perfectly fine.