Using Oracle stored procedures

Wherever procedures are allowed in IBM® Lotus Enterprise Integrator® (LEI) or Domino® Enterprise Connection Services (DECS), an Oracle stored procedure may be used such as the destination of a Direct Transfer or within an Lotus® Connector LotusScript® Extensions (LC LSX) script. The stored procedure being called must have its parameters set up correctly for the call.

Input values are provided to Oracle procedures as named parameters. This requires that the parameters in Oracle use the same names as the LEI or DECS fields being provided as input values. The inputs being provided should include key values when being used in the context of a keyed operation (selection, update, or delete context), and data values when relevant (insert or delete context). The input value data types provided by the Oracle connector are selected as the closest match to the data type in the LEI or DECS system, and will be converted by Oracle to the procedure parameter defined data types, as long as the conversion is supported by Oracle.

Note: Oracle limits LONG data types to 32K for stored procedures.
Note: The Lotus® Connector for Oracle does not support LOB data types for stored procedures.
Note: Calling an Oracle stored procedure in a Command activity requires the following syntax:
BEGIN PROCNAME(); END;

The Lotus® Connector for Oracle and Output Parameters

The Oracle Connector supports output parameters as the way of returning results from a stored procedure. This requires additional information to be available at the time the procedure is called, specifically the context of the call and the output parameter names. This information is automatically provided by LEI or DECS activities, but must be manually specified when calling Oracle procedures from an LC LSX script. The context indicates whether the procedure should expect and specify output parameters, and the parameter names are provided as a property of the procedure call request to the Oracle connector. The output parameters must be standard data types; row sets may not be returned. This restricts the result set from an Oracle procedure to a single row. Any parameters that are indicated as input parameters and which are also in the output parameter list will be provided as input/output parameters.