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.
Note: Typically, you can have only fifty stored procedures running at one time. This number, however, is further restricted by the number of open cursors you specified for your Oracle database or for the current session. See Index cursors for information on specifying open cursors. Cursor limitations also vary across platforms. See your Oracle documentation for more information.
