This section presents more examples that show various techniques to code temp-tables.

Executing a stored procedure call using the LOAD-RESULT-INTO phrase

This example shows the basics of executing a call to a stored procedure using the functionality that the LOAD-RESULT-INTO phrase supports. Note that the code works with the stored procedure in Example comparable to the way you can see it works with ABL code.

/* Calling a stored procedure, using a static temporary table */

DEFINE VARIABLE ttArray AS HANDLE EXTENT 2.

DEFINE TEMP-TABLE tt1
  FIELD custnum AS INTEGER
  FIELD name    AS CHARACTER.

DEFINE TEMP-TABLE tt2.
  FIELD ordernum  AS INTEGER
  FIELD orderdate AS DATE.

ASSIGN ttArray[1] = TEMP-TABLE tt1 HANDLE
       ttArray[2] = TEMP-TABLE tt2:HANDLE.

RUN STORED-PROC pcustorder LOAD-RESULT-INTO ttArray 
  (INPUT 10, OUTPUT 0, OUTPUT 0).

Using an existing temp-table without the TEMP-TABLE-PREPARE ( ) method

This example shows the basics of using an existing dynamic temp-table without the TEMP-TABLE-PREPARE () method. In this instance, the send-sql-statement option is used rather than a predefined stored proc. In contrast, the third example code that appears later in this section shows the same approach, but explicitly defines the existing dynamic temp-table with the TEMP-TABLE-PREPARE() method.

/* Calling a stored procedure, using an existing temp-table without temp table
   prepare */

DEFINE VARIABLE ttHndl AS HANDLE NO-UNDO.

CREATE TEMP-TABLE ttHndl.

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO ttHndl 
  ("select * from customer").

Using an existing temp-table with the TEMP-TABLE-PREPARE ( ) method

This example shows the basics of using an existing dynamic temp-table with the TEMP-TABLE-PREPARE() method.

/* Calling a stored procedure, using an existing temp-table with temp table
   prepare*/

DEFINE VARIABLE ttHndl AS HANDLE NO-UNDO.

CREATE TEMP-TABLE tt1Hndl.
ttHndl:ADD-NEW-FIELD("custNum","integer").
ttHndl:ADD-NEW-FIELD("name","char").
ttHndl:TEMP-TABLE-PREPARE("ordX").

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO ttHndl 
  ("selec custNum, name from myDB.customer").

Note these points as they relate to the above example:

  • As a prerequisite for creating the code shown in the previous example, the developer would need to define the schema for the table.
  • Once the temp-table schema begins preparation from the clear state, the temp-table must be defined to the exact specifications for the result sets as generated by the RUN STORED-PROC statement or send-sql-statement option. Otherwise, the RUN STORED-PROC will end in failure. Also, note the TEMP-TABLE-PREPARE must be called at the completion of the temp-table definition associated with the stored procedure results.

A stored procedure that uses the LOAD-RESULT-INTO phrase with a single temp table and the send-sql-statement option

This example shows the syntax for the stored procedure with the LOAD-RESULT-INTO phrase with a single static temp-table and the send-sql-statement option.

/* Calling a stored procedure that uses the LOAD-RESULT-INTO phrase with a
   single temp table and the send-sql-statement option */

DEFINE VARIABLE ttHandle AS HANDLE NO-UNDO.

DEFINE TEMP-TABLE tt1
  FIELD f1 AS INTEGER
  FIELD f2 AS CHARACTER.

ttHandle = TEMP-TABLE tt1:HANDLE.
RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO ttHandle
  ("Select cust_num, name from customer").

Using the PROC-STATUS phrase

This example shows the use of a PROC-STATUS phrase. The PROC-STATUS phrase must be defined as part of the RUN STORED-PROC statement because of the implicit CLOSE STORED-PROC that is associated with the LOAD-RESULT-INTO phrase.

/* Example of the implicit close stored-proc and use of LOAD-RESULT-INTO */
 DEFINE VARIABLE iStat AS INTEGER NO-UNDO. 
DEFINE VARIABLE ttHandle AS HANDLE  NO-UNDO. 

CREATE TEMP-TABLE ttHandle 
RUN STORED-PROCEDURE pcust LOAD-RESULT-INTO ttHandle  
   iStat = PROC-STATUS (20, OUTPUT 0, OUTPUT 0). 
DISPLAY iStat.

In this example, note that the PROC-STATUS phase does not need a PROC-HANDLE phrase because it is retrieved using the RUN STORED-PROC statement and not after this statement's execution as it typically is used.

Executing a call to a stored function that returns a result set using the LOAD-RESULT-INTO phrase.

This example is a two-part example that shows the basics of executing a call to a stored function that returns a result set using a cursor and the functionality that the LOAD-RESULT-INTO phrase supports. The result set will be loaded into a temp-table.

/* First part of the procedure - Stored-function code*/

create or REPLACE function myfunc_2 (num in number) return cv_types. 
GenericCurType is c1 cv_types.GenericCurType;
begin

OPEN c1 FOR
   select *
     from customer where cust_num>=num;

return c1;
end;
/
 
/* Second part of the procedure - ABL code*/

DEFINE VARIABLE tth AS HANDLE NO-UNDO.

CREATE TEMP-TABLE tth.
RUN STORED-PROC myfunc_2 LOAD-RESULT-INTO tth (OUTPUT ?, INPUT 10).