A PL/SQL table can be populated from a block using Forms'implicit populating built-in TABLE_FROM_BLOCK. This very handy feature eliminates your having to loop through the block explicitly. The following procedure illustrates the concept:
PROCEDURE populate_plsql_table(my_table1 my_table, cnt OUT NUMBER) IS /* Define a PL/SQL record with two fields code and name */ TYPE state_rec IS RECORD (code varhcar2(2), name varhcar2(30)); /* Define a PL/SQL table of the record defined above */ TYPE my_table IS TABLE OF state_rec INDEX BY BINARY_INTEGER; my_table1 my_table; /* Define a variable of type ITEMS_IN_BLOCK. ITEMS_IN_BLOCK is a Forms-defined table */ Item_data ITEMS_IN_BLOCK; Cnt NUMBER; BEGIN Item_data(1) := 'STATE_CODE'; item_data(2) := 'STATE_NAME'; /* The call to the Forms built-in TABLE_FROM_BLOCK retrieves the records from the block and populates the my_table1 table of records */ TABLE_FROM_BLOCK(my_table1, 'STATE',1, ALL_RECORDS, item_data); -- The SUCCESS or FAILURE of this built-in can be assessed -- with FORM_SUCCESS, just like any other built-in IF NOT FORM_SUCCESS THEN RAISE FORM_TRIGGER_FAILURE; END IF; Cnt := my_table1.COUNT; END populate_plsql_table;
To use this technique, follow these steps:
The Oracle documentation defines the PL/SQL table to be of type PLITBLM.TABLE_OF_ANY, but defining the PL/SQL table in the manner I've described works well.
To pass all the records in the block, specify 1 as the starting record number and the constant ALL_RECORDS as the end record number.
The Success or Failure of TABLE_FROM_BLOCK
The success or failure of TABLE_FROM_BLOCK can in most cases be trapped by FORM_SUCCESS. However, there are exceptions. One such exception is the error FRM-40733: PL/SQL built-in TABLE_FROM_BLOCK failed.
In this case, it is not one of FORM_SUCCESS, FORM_FAILURE, or FORM_FATAL. ON-ERROR is often a handy alternative to FORM_SUCCESS for tracking the success or failure of TABLE_FROM_BLOCK and in this example, too, helps us in trapping this error. The preceding error occurs when a nonexistent block name is passed or negative values are passed for the starting or starting and ending record positions .
Another important point to note is that TABLE_FROM_BLOCK implicitly loops through the block, so POST-QUERY is executed for each record. However, it's faster than the manual looping. For a result set of 3,300 records, it was seen to be 3.5 times faster than the manual looping with Oracle 8.0.5 running on Windows NT.
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