Calling a stored procedure

To call a stored procedure with execute, use the #sql directive and specify the name of the stored procedure in the explicit SQL:

execute #sql{
    CALL MYSTOREDPROCEDURE
};

If the stored procedure accepts parameters, pass EGL variables as host variables (see Host variables):

myParameter int = 5;
execute #sql{
    CALL MYSTOREDPROCEDURE(:myParameter)
};

The following example uses a prepared statement in combination with the execute statement:

prepare p1 from "CALL MYSTOREDPROCEDURE(?)";
execute p1 using myParameter;

To call a stored procedure with open, use the #sql directive and specify the name of the stored procedure in the explicit SQL:

open myResultSet with #sql{
    CALL GETCUSTOMERS
};

Then you can access the result set through the myResultSet identifier, as in the following example, which assumes a Record part (stereotype SQLRecord) named MyCustomerPart:

myCustomers MyCustomerPart;
get next from myResultSet into myCustomers;

If the stored procedure accepts parameters, pass EGL variables as host variables:

myParameter int = 5;
open myResultSet with #sql{
    CALL GETCUSTOMERS(:myParameter)
};

The following example uses a prepared statement in combination with the open statement:

prepare p1 from "CALL GETCUSTOMERS(?)";
open myResultSet with p1 using myParameter;

To call a stored procedure with get, use the #sql directive and specify the name of the stored procedure in the explicit SQL:

myCustomers MyCustomerPart[]{};
get myArray with #sql{
    CALL GETCUSTOMERS
};

If the stored procedure accepts parameters, pass EGL variables as host variables:

myCustomers MyCustomerPart[]{};
myParameter int = 5;
get myArray with #sql{
    CALL GETCUSTOMERS(:myParameter)
};

The following example uses a prepared statement in combination with the get statement:

myCustomers MyCustomerPart[]{};
myParameter int = 5;
prepare p1 from "CALL GETCUSTOMERS(?)";
get myCustomers with p1 using myParameter;

There are specific rules that apply to using the open or get statement to call a stored procedure when you use an Oracle database:

• The procedure must have at least one parameter.
• The first parameter must be have an out or inOut modifier, and must be a REF CURSOR type (an example of how to define this type is shown later). A variable based on this type, called a cursor variable, can pass result sets between the parts of a program.
• Represent the first parameter of the procedure with a question mark, as shown in later examples.
• If the procedure call is in a prepared statement, do not include anything in the using clause for the first parameter. If there are no other parameters, omit the using clause.

In the first example, using the #sql directive, the procedure has no parameters except the cursor variable:

open rs1 with #sql { call p1( ? ) };

In the next example, also using #sql, the procedure has two parameters in addition to the cursor variable:

x int = 10;
y int = 1000;
open rs2 with #sql { call p2( ?, :x, :y ) };

In the next example, using a prepared statement, the procedure has no parameters except the cursor variable:

prepare pstmt3 from "call p1( ? )";
open rs3 with pstmt3;

In the next example, using a prepared statement, the procedure has two parameters in addition to the cursor variable:

prepare pstmt4 from "call p2( ?, ?, ? )";
open rs4 with pstmt4 using x, y;

The following example shows one way to define a REF CURSOR type to Oracle. (For other ways, refer to your Oracle documentation.)

execute #sql{ CREATE OR REPLACE PACKAGE MYPKG
   AS
      TYPE RC12 IS REF CURSOR;
   END; };

The preceding code creates a new type named MYPKG.RC12 that you can use for the type of a parameter that holds the results of a query. The following EGL code defines a stored procedure that you can call using an EGL open statement:

execute #sql{ CREATE PROCEDURE ZPQPRM2( c IN OUT MYPKG.RC12, x IN CHAR ) 
   AS
   BEGIN
      OPEN c FOR SELECT firstnme, empno FROM empx WHERE empno > x ORDER BY empno;
   END; };

In this first example, the called function is passed a string and returns an integer:

x int;
y string = "hello";
execute #sql{ call :x := func1( :y ) };
writeStdout( "The function returned " :: x );

This next example calls the same function using a prepared statement:

prepare q from "call ? := func1( ? )";
execute q using x, y;
writeStdout( "The function returned " :: x );

EGL can call Oracle stored functions that return the results of a query. The following rules apply:

• Call the function with an EGL open or get statement.
• The function must return a REF CURSOR type.
• The SQL code that an open statement runs must include a question mark to represent the value that the function returns.
• If the function call is in a prepared statement, do not include anything in the using clause for the first question mark. If the function has no parameters, omit the using clause.

In the first example, using the #sql directive, the function has no parameters except the cursor variable:

open rs5 with #sql { call ? := f5() };

In the next example, also using #sql, the function has two parameters in addition to the cursor variable:

x int = 10;
y int = 1000;
open rs6 with #sql { call ? := f6( :x, :y ) };

In the next example, using a prepared statement, the function has no parameters except the cursor variable:

prepare pstmt7 from "call ? := f5()";
open rs7 with pstmt7;

In the next example, using a prepared statement, the function has two parameters in addition to the cursor variable:

prepare pstmt8 from "call ? := f6( ?, ? )";
open rs8 with pstmt8 using x, y;