IBM Integration Bus, Version 9.0.0.8 Operating Systems: AIX, HP-Itanium, Linux, Solaris, Windows, z/OS

See information about the latest product version

Invoking stored procedures

To invoke a procedure that is stored in a database, use the ESQL CALL statement. The stored procedure must be defined by a CREATE PROCEDURE statement that has a Language clause of DATABASE and an EXTERNAL NAME clause that identifies the name of the procedure in the database and, optionally, the database schema to which it belongs.

When you invoke a stored procedure with the CALL statement, the broker ensures that the ESQL definition and the database definition match:

The external name of the procedure must match a procedure in the database.
The number of parameters must be the same.
The type of each parameter must be the same.
The direction of each parameter (IN, OUT, INOUT) must be the same.

The following restrictions apply to the use of stored procedures:

Overloaded procedures are not supported. (An overloaded procedure is one that has the same name as another procedure in the same database schema with a different number of parameters, or parameters with different types.) If the broker detects that a procedure is overloaded, it raises an exception.
In an Oracle stored procedure declaration, you are not permitted to constrain CHAR and VARCHAR2 parameters with a length, and NUMBER parameters with a precision or scale, or both. Use %TYPE when you declare CHAR, VARCHAR, and NUMBER parameters to provide constraints on a formal parameter.
Avoid changing the type or number of parameters of a procedure without also changing the name. If changes of this nature cannot be avoided, any integration servers with procedures with changed parameters called by the ESQL must be restarted.

Creating a stored procedure in ESQL

When you define an ESQL procedure that corresponds to a database stored procedure, you can specify either a qualified name (where the qualifier is a database schema) or an unqualified name.

To create a stored procedure:

Code a statement similar to this example to create an unqualified procedure:
```
CREATE PROCEDURE myProc1(IN p1 CHAR) LANGUAGE DATABASE EXTERNAL NAME "myProc";
```
The EXTERNAL NAME that you specify must match the definition that you have created in the database, but you can specify any name you choose for the corresponding ESQL procedure.

Code a statement similar to this example to create a qualified procedure:

CREATE PROCEDURE myProc2(IN p1 CHAR) LANGUAGE DATABASE EXTERNAL NAME "Schema1.myProc";

Code a statement similar to this example to create a qualified procedure in an Oracle package:

CREATE PROCEDURE myProc3(IN p1 CHAR) LANGUAGE DATABASE EXTERNAL 
                 NAME "mySchema.myPackage.myProc";

For examples of stored procedure definitions in the database, see the CREATE PROCEDURE statement.

Calling a stored procedure

Code a statement similar to this example to invoke an unqualified procedure:
```
CALL myProc1('HelloWorld');
```
Because it is not defined explicitly as belonging to any schema, the myProc1 procedure must exist in the default schema (the name of which is the user name that is used to connect to the data source) or the command fails.
The following example calls the myProc procedure in schema Schema1.
```
CALL myProc2('HelloWorld');
```
Code a statement similar to this example to invoke an unqualified procedure with a dynamic schema:
```
DECLARE Schema2 char 'mySchema2';
CALL myProc1('HelloWorld') IN Database.{'Schema2'};
```
This statement calls the myProc1 procedure in database Schema2, overriding the default "username" schema.

Calling a stored procedure that returns two result sets

To call a stored procedure that takes one input parameter and returns one output parameter and two result sets:

Define the procedure with a CREATE PROCEDURE statement that specifies one input parameter, one output parameter, and two result sets:
```
CREATE PROCEDURE myProc1 (IN P1 INT, OUT P2 INT)
  LANGUAGE DATABASE
  DYNAMIC RESULT SETS 2
  EXTERNAL NAME "myschema.myproc1";
```

To invoke the myProc1 procedure by using a field reference, code:

/* using a field reference */
CALL myProc1(InVar1, OutVar2, Environment.ResultSet1[], 
            OutputRoot.XMLNS.Test.ResultSet2[]);

To invoke the myProc1 procedure by using a reference variable, code:

/* using a reference variable*/
DECLARE cursor REFERENCE TO OutputRoot.XMLNS.Test;

CALL myProc1(InVar1, cursor.OutVar2, cursor.ResultSet1[], 
                     cursor.ResultSet2[]);

ac17040_.htm |

Last updated Friday, 21 July 2017