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 Examples based on views created in the data source 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).