Tips for Standard GUI Practices and Forms Development

PL SQL8 and 8i in Forms

This section briefly discusses which PL/SQL 8 and 8i features are supported by Forms 6.0 and which features are not.

PL/SQL8 and 8i in Forms 6.0

Client side PL/SQL does support PL/SQL 8, and some of the server-side PL/SQL 8 features can be used from the Forms side. Also large objects(LOBs) can be used. The following PL/SQL 8 features can be used as part of Forms-side PL/SQL:

• VARRAYS involving scalar data-types or record types.

  For example, consider the folowing VARRAY declaration:

  DECLARE TYPE num_array IS VARRAY(10) OF NUMBER; v_arr num_array := num_array(101, 201, 301, 401); BEGIN IF v_arr(2) IS NOT NULL THEN MESSAGE('v_arr(2) is NOT NULL'); PAUSE; END IF; END;

  This is valid in Forms 6.0

• Nested tables involving built-in types or an expression using %ROWTYPE.

  For example, consider the nested table declaration in Forms:

  DECLARE TYPE num_table IS TABLE OF NUMBER; v_num num_table := num_table(101, 201, 301, 401); BEGIN /* This below assignment will replace the value 101 previously assigned to the first element */ v_num(1) := 99999; END;

  This is valid in Forms 6.0

• Direct DML on object tables and database tables having VARRAYS and nested table columns .

  Consider the following scenario:

  create or replace type addlist as varray(10) of add_type; create table source_locator(id number(6), src_loc addlist);

  The following PL/SQL block is valid inside any appropriate trigger in Forms 6.0:

  begin insert into source_locator values (1, addlist2(add_type('A','B','C','D','E'), add_type('A1','B1','C1','D1','E1'))); end;

The following are not possible in Forms 6.0:

• Direct declaration of object variables
• Direct instantiation of an object using constructor methods
• Direct manipulation of an object using member methods
• Direct declaration and PL/SQL operations involving VARRAYS and nested tables involving object types stored in the database

For example, the following cannot be used:

DECLARE address1 add_type; BEGIN END;

Also, based on the above VARRAY based object type addlist, the following cannot be used:

DECLARE v_src_loc addlist; BEGIN .... END;

To indirectly perform DML on objects from Forms, wrap the PL/SQL8 code using stored procedures that return an Index-By table of records and call these outer procedures from inside triggers or directly against a DML source by basing form blocks on them.

Oracle 8 SQL operators and functions can be used to access objects from Forms. For example, the operators REF, DEREF, VALUE, MAP, and SELF can be used in Forms code.

Also you can use GLOBAL_SCOPE record groups instead of VARRAYS and nested tables to share data arrays among forms.

Pass Index-by table of records to account for object refs and parameters of object values, as explained in the section "Basing a Block on a Stored Procedure Involving Object Tables."

Also the PLSQL 8i features such as invoker rights, native dynamic SQL, and autonomous transactions are not supported by Forms 6.0. But invoking JavaBeans methods and incorporating PJCs (Pluggable Java Components) is supported from Forms 6.0.

PL/SQL Features Common to Oracle 8 and 8i

The analog of INSTEAD-OF triggers, in Forms, is built-in by means of ON-INSERT, ON-UPDATE, and ON-DELETE triggers.

The ON-INSERT, ON-UPDATE, and ON-DELETE triggers, also called transactional triggers, are for data blocks based on tables (object or non-object), blocks based on in-line SELECT s ( FROM clause queries), and views and blocks based on multiple tables (directly specified in the DML data source separated by a comma and with a WHERE condition).

These are analogous to the FOR EACH ROW INSTEAD-OF triggers provided at the database level for performing DML ( INSERT, UPDATE, DELETE ONLY ) operations on views based on multiple tables.

Also, direct DML is possible in blocks based on views for which corresponding INSTEAD-OF triggers have been defined at the database level. In this case, there is no need for the ON- transactional triggers mentioned above.

Also note that from Forms 5.x on, there are five new triggers INSERT-PROCEDURE, UPDATE- PROCEDURE, DELETE-PROCEDURE, LOCK-PROCEDURE, and SELECT-PROCEDURE for implementing DML on a data block based on a stored procedure. These triggers are automatically generated when stored procedures are provided against the respective DML source names , and they cannot be modified.