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)


/* 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 */


 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;



 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




 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.


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.


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.

GUI Development

Advanced GUI Development: Developing Beyond GUI

Multi-form Applications

Advanced Forms Programming

Error-Message Handling

Object-oriented Methods in Forms

Intelligence in Forms

Additional Interesting Techniques

Working with Trees

Oracle 8 and 8i Features in Forms Developer

Oracle Developer Forms Techniques
Oracle Developer Forms Techniques
ISBN: 0672318466
EAN: 2147483647
Year: 2005
Pages: 115

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net