Creating a temp-table layout plan

DataServer for Oracle

RDBMS Stored Procedure Details : Interfacing with RDBMS stored procedures : Loading a result set into a temp-table : Creating a temp-table layout plan

You must define the temp-table layout in your application program to accommodate specific result sets before you attempt to populate the temp-tables with data. If a SQL statement retrieves more than one result set, you must define multiple temp-tables to be able to retrieve all the data. Therefore, the success of this approach depends to a large extent on your:

Understanding of the specific data your foreign data source is providing you through a given stored procedure

Ability to correctly define temp-tables

The following types of temp-tables can support result sets:

Static — A temp-table whose schema is defined at compile time.

Dynamic — A temp-table whose schema is defined at run time. There are two types of dynamic temp-tables: dynamic-prepared and dynamic-unprepared.

Keep in mind that you can pass handles of temp-tables that contain a mixed array. A mixed array is one in which some of the temp-table handle elements can be static while others can be dynamic. Also, note that a stored procedure supports the use of an INT64 data type in static and dynamic temp tables when the LOAD-RESULT-INTO phrase processes the procedure's result set on the RUN-STORED-PROC statement.

The following table identifies the temp-table options for which you can plan and the requirements you must fulfill for each option.

Table 22. Options to plan the temp-table layout for result sets
To return a result set to this type of temp-table . . .	Then the layout definition is . . .
StaticDynamic-prepared state	Defined by you; you must base the layout on the expected fields to be returned and each of these fields' data types so that the first field defined in the temp-table corresponds to the first column of the result set. This column matching and data type matching must be repeated successfully for each temp-table and its corresponding result set.
Dynamic - unprepared state	Not defined by you; the schema of the temp-table is based on the result-set schema and a mapping of default ABL data types for each SQL type. For more information, see Details about a dynamic temp-table in an unprepared state.

Note: Once the data is loaded into the temp-table, any updates made to the records in the temp-table are not propagated back to the foreign database. Result sets are available through temp-tables for the purpose of obtaining a snapshot of the data. For example, you can use this technique to populate a browser from a temp-table. You must re-read the record using the proper lock mechanism to actually update the record.