Interfacing with RDBMS stored procedures

As previously mentioned, the RUN STORED-PROCEDURE statement is the initial statement required for the execution of all stored procedures you define and initiate through OpenEdge. It can run an RDBMS stored procedure bound by the data source, or allow you to send SQL to an Oracle Server data source using an OpenEdge DataServer. The specific, additional keywords and elements you must include in a stored procedure or define for a send-sql statement depend on the stored procedure characteristics including its signature, data results, and methods chosen to retrieve output to ABL procedure.

This section identifies and describes the syntax elements, keywords, and other requirements associated with obtaining stored procedure output from a stored procedure data request.

The following sections describe how to run Oracle stored procedures and retrieve return codes, output parameter values, and results sets.

Oracle stored procedure pcust

This example is the basis for the following examples of how to run stored procedures in ABL using this stored procedure created in Oracle.

CREATE PROCEDURE pcust (num IN INT, orders OUT INT, states OUT INT) 
AS BEGIN 
IF num IS NULL THEN
  raise_application_error (-20101, ‘Cust Num is missing');
ELSE
  SELECT COUNT (*) INTO orders FROM customer, order_
    WHERE customer.Cust_num = order_.Cust_num AND customer.Cust_num > num;
SELECT count(*) INTO states FROM customer WHERE cust_num > num;
END IF;
END;

This PL/SQL code creates the stored procedure pcust and defines three parameters: num, orders, and states. The orders and states parameters are output parameters, which means that the procedure returns values for these parameters to the caller. All the parameters are of the data type INTEGER.