Populating a PL/SQL Table from a Block

Populating a PL SQL Table from a Block

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:

1. Define a PL/SQL record to be equivalent to the record structure to be passed as input. In this case, it is state_rec and constitutes the two items CODE and NAME corresponding to STATE_CODE and STATE_NAME.
2. Define the PL/SQL table to be a table of records of the type defined in step 1. In this case, it is my_table.

Note

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.

 

1. Define a variable of type ITEMS_IN_BLOCK (a table of VARCHAR2 ) and set its individual elements to be the names of the block item names whose values figure as elements of the record type defined in step 1.
2. Call the built-in TABLE_OF_ANY with the defined PL/SQL table, input block name, start record number, end record number, and the variable defined in step 3 passed as parameters.

Tip

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 .

Tip

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.