Try OpenEdge Now
skip to main content
DataServer for Oracle
RDBMS Stored Procedure Details : Interfacing with RDBMS stored procedures : Loading a result set into a temp-table : Details about a dynamic temp-table in an unprepared state
Details about a dynamic temp-table in an unprepared state
A dynamic temp-table is considered to be in an unprepared state after the first definitional method is called until the temp-table is prepared. If a clear dynamic temp-table handle is passed, the DataServer populates the temp-table schema based on the result-set schema and prepares the temp-table. A clear dynamic temp-table is a table that is in an unprepared state where definitional methods have not yet been called. The DataServer then executes the temp-table handle: ADD-NEW-FIELD (field name, data type) internally for each one of the columns of the result set. If a column in the result set from the foreign schema does not have a name (for example, an array element field or a SQL-derived field), the DataServer assigns a name to it based on the column position on the row.
For example, if you run the following statement, then the temp-table contains columns custnum, name and column4:
/* */
SELECT "mytag", custnum, name, (salary * 0.10) FROM <anytablename>
The data type associated with each column follows the mapping rules that exist between OpenEdge and the foreign data source's data types. For more information about data types and default mapping, see Initial Programming Considerations
Note: Since a stored procedure can return multiple result sets, the DataServer prepares the temp-table as "result<n>" where <n> is the result-set sequence number that corresponds to its element position in the temp table handle array, starting with 1. Therefore, if there are 3 result sets and 3 clear dynamic temp-tables are passed, the temp-tables are called result1, result2, and result3.
Note the following error conditions as they specifically apply to a dynamic temp-table:
*If an error occurs during the schema population or during the prepare of a temp-table, the DataServer raises an error condition.
*The dynamic temp-table must be either already prepared or clear with no defined fields in it. If fields have already been added to a dynamic temp-table before the RUN STORED-PROC statement is executed and the temp-table is not prepared, the DataServer raises an error condition due to the invalid state of the temp-table.