Syntax

The examples in this section do not use the supplied proc-text-buffer buffer. Instead, they show how to define formatted buffers by creating views in the data source, using the following syntax:

CREATE VIEW BUFFER_buffer-name

The following two examples show the views created in your Oracle data source that you can use as buffers to store the results from the stored procedure pcust:

CREATE VIEW BUFFER_pcust_orders AS SELECT customer.custnum, 
  customer.name, order.ordernum FROM customer, order WHERE 1 = 0
 
CREATE VIEW BUFFER_pcust_states AS SELECT customer.custnum, state.state 
  FROM customer, state WHERE 1 = 0

Running the send-sql-statement option twice

This example runs the send-sql-statement option twice; procedure handles (through the PROC-HANDLE function) identify the different results from the Oracle database.
Note: This example is not intended to illustrate the use of the previous syntax. 
DEFINE VARIABLE cSelect  AS CHARACTER NO-UNDO.
DEFINE VARIABLE iHandle1 AS INTEGER   NO-UNDO.
DEFINE VARIABLE iHandle2 AS INTEGER   NO-UNDO.

DEFINE BUFFER bfCustomer FOR customer.

RUN STORED-PROC send-sql-statement iHandle1 = 
 PROC-HANDLE ("SELECT custnum, state FROM customer").

FOR EACH bfCustomer WHERE PROC-HANDLE = iHandle1:
  cSelect = "SELECT state, statename, region FROM state WHERE state = '" +
             bfCustomer.state + "'".

  RUN STORED-PROC send-sql-statement iHandle2 = PROC-HANDLE (cSelect).
  FOR EACH bfCustomer WHERE PROC-HANDLE = iHandle2:
    DISPLAY bfCustomer.
  END.
  CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = iHandle2.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = iHandle1.

If you use more than one send-sql-statement at a time to send SELECT statements, you must explicitly define procedure handles for each.

A stored procedure returning multiple result sets and how to access the result sets using cursor arguments and buffers

The following example is a two-part example. The code in the first part shows a stored procedure. The code in the second part shows the ABL procedure that is associated with the stored procedure. 

/* First part of example - Stored procedure code */

create or replace package cv_types as
  type GenericCurType is ref cursor;
End cv_types;
/CREATE or replace PROCEDURE pcustorder (num IN INT, c1 out cv_types.
   GenericCurType, c2 out cv_types.GenericCurType)
AS BEGIN
IF num IS NULL THEN
raise_application_error (-20101, ‘Cust Num is missing');
ELSE 

open c1 for
SELECT cust_num,name FROM customer WHERE cust_num > num;

open c2 for
SELECT order_num,odate FROM order_WHERE order_.Cust_num > num;

END IF;
END;
/

The ABL procedure code in this example assumes that the pcust_buffer and porder_buffer exist in the schema and that they match the result-sets schema.

/* Second part of example - ABL procedure code*/

DEFINE VARIABLE hProc AS HANDLE NO-UNDO.

RUN STORED-PROC pcustorder 
  (INPUT 10, OUTPUT 10, OUTPUT 10, OUTPUT 10, OUTPUT 10).
FOR EACH pcust_buffer WHERE PROC-HANDLE = hProc AND CURSOR = pcustorder.c1:
  DISPLAY pcust_buffer.
END.
FOR EACH porder_buffer WHERE PROC-HANDLE = hProc AND CURSOR = pcustorder.c2:
  DISPLAY porder_buffer.
END.
CLOSE STORED-PROC pcustorder.

As the above example shows, the stored procedure pcustorder returns multiple result-sets. The second code box shows the procedure used to access the result sets; it uses cursor arguments and buffers.

Because two different buffers have been defined, the returned values maintain their data types instead of being converted to character strings and stored in an ABL-defined buffer proc-text-buffer. You can then use the returned values in calculations without first converting them back to their original data types. In addition, the two separate buffers make your output look cleaner, allowing ABL to build a new default frame for the two different types of output. Reading your results into an explicitly defined buffer also allows you to manipulate the data just as you would manipulate data from an OpenEdge database; for example, with Frame phrases and FORM statements.