Techniques for Querying and Updating New Data Sources in Forms Based on Objects

Oracle 8 introduced object tables (tables containing row objects) and tables having column objects. This section describes techniques for performing DML on blocks based on object tables and column objects.

CRUD on Object Table Based Blocks in Forms

Blocks in forms can be based on object tables that are based on user-defined types. A user-defined data type in Oracle 8 and 8i can be either an ADT (an abstract data type) or an object type. The latter can include member functions along with the type structure definition. Henceforth, I will refer to either of these as a UDT ( user -defined type).

Oracle Forms explodes a UDT into its individual components . So INSERT, UPDATE, DELETE, SELECT, and LOCK boil down to these operations being performed on a normal base table item. Consider the following example:

CREATE OR REPLACE TYPE add_type AS OBJECT

 (street VARCHAR2(20),

 city VARCHAR2(20),

 state VARCHAR2(2),

 zip VARCHAR2(13));

CREATE TABLE address OF add_type;

Here add_type is a UDT consisting of four individual fields, namely, street, city, state, and zip. The database table address is based on this UDT. A block in forms based on the address table explodes the underlying object-type into four base-table items, each item corresponding to the respective individual field in the object type. An INSERT or UPDATE operation in this block generates an OID (object ID) for each row being inserted or deleted.

CRUD on Object-Type Based Items in Forms (Column Object)

Blocks in forms can be based on relational tables having user-defined type based columns . A user-defined data type in Oracle 8 and 8i can be either an ADT or an object type. The latter can include member functions along with the type structure definition. Henceforth, I will refer to either of these as a UDT.

Oracle Forms explodes a column UDT into its individual components. So INSERT, UPDATE, DELETE, SELECT, and LOCK boil down to these operations being performed on a normal base table item. (They are similar to the operations done on relational base table items.)

Item names based on UDTs are constructed as the UDT_name suffixed by an underscore and the exploded item name .

Consider the following example of the person table having two object columns off_add and home_add, based on the object type created earlier:

CREATE TABLE person

(id number(10) PRIMARY KEY,

 lastname varchar2(30) NOT NULL,

 firstname varchar2(30) NOT NULL,

 middle_initial varchar2(2) ,

 off_add add_type,

 home_add add_type);

The PERSON block containing two object-column based items OFF_ADD and HOME_ADD has its components exploded. For example, Forms creates four text items out of OFF_ADD : OFF_ADD_STREET, OFF_ADD_CITY, OFF_ADD_STATE, and OFF_ADD_ZIP.

No composite item ”that is, one consisting of the entire object column ”is created for an object-column.

The relevance of the exploded object-column items is no more than an ordinary base table item. Forms treats them just like ordinary items.

Direct and indirect referencing are valid for the individual component types. This means that direct selection and assignment of an exploded form item based on an object column and also using NAME_IN and COPY, are allowed.

Forms facilitates implicit instantiation, manipulation, and storage of underlying objects with the ease of a relational table.

DML statements based on objects in Forms are referenced as follows :

• Using REF operator
• Using the exploded item value
• Using the dot ( . ) notation