Federated procedures and Oracle

You can create a federated procedure and a function mapping for the same Oracle function.

Use a function mapping when you need to use the Oracle function in SQL as a scalar function. Use a federated procedure when you need to use the Oracle function in CALL statements.

Oracle returns only a single value for functions. The return value for the federated procedure appears at the beginning of the parameter list as an extra OUT parameter. The name of the parameter is always DEFAULT. When you specify the NUMBER OF PARAMETERS clause in the CREATE PROCEDURE (Sourced) statement, count the return values if you are using Oracle version 9i or later.

For some Oracle data types, information about the precision, length, and scale is not stored in the Oracle catalog when the parameters of a procedure are declared. When a federated procedure is created, information about the Oracle procedure is gathered from the Oracle catalog. Because information about the precision, length, and scale is not stored in the Oracle catalog, federated procedures behave in the following way:

Use the maximum length for the parameter data types.
Map the Oracle NUMBER data types to the federated DOUBLE data type. You can change this mapping by overriding the default forward data type mapping for Oracle NUMBER.

Tip: Overriding the default forward data type mappings affects other federated DDL operations, such as CREATE NICKNAME. Therefore, before you create the procedure, change the type mapping. Create the procedure for the Oracle procedure with the new type mapping, and then DROP the new type mapping. Subsequent nicknames and procedures that you create will use the default type mapping.

Example

This example shows how to use the CREATE PROCEDURE statement to create a federated procedure for a data source procedure on Oracle. You can issue the CREATE PROCEDURE statement from the Db2® command line or create a federated procedure in IBM® Data Studio.

CREATE PROCEDURE PROC2 SOURCE ZELLER_SCHEMA.ORACLE_PKG9.PROC2
     NUMBER OF PARAMETERS 5 UNIQUE_ID '2' FOR SERVER ORA_SERVER
     SPECIFIC MYPROC1 WITH RETURN TO CLIENT ALL
     MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION;

PROC2: Required. Specifies the name of the federated procedure.
SOURCE ZELLER_SCHEMA.ORACLE_PKG9.PROC2: Required. Specifies the schema, package, and name for the Oracle procedure or function. If the Oracle procedure or function is in a package, you must specify a three-part name in the CREATE PROCEDURE statement. The format for this three-part name is source_schema_name.source_package_name.source_procedure_name. If the Oracle procedure or function is not in a package, you must specify a two-part name in the CREATE PROCEDURE statement. The format for this two-part name is source_schema_name.source_procedure_name.
NUMBER OF PARAMETERS 5: Specifies the total number of IN, OUT, and INOUT parameters that the Oracle procedure uses. Use this parameter when you have more than one procedure with the same schema name and procedure name. For example, if your schema is ZELLER and you have a PROC1 procedure with two parameters and another PROC1 procedure with three parameters, the name for both of these procedures is ZELLER.PROC1. The value for the NUMBER OF PARAMETERS in the data source procedure indicates which procedure you refer to in the CREATE PROCEDURE statement. Oracle REFCURSOR parameters must be included in the NUMBER OF PARAMETERS count.
UNIQUE_ID '2': Specifies the unique identifier for the Oracle procedure. Use the UNIQUE_ID parameter only when the schema name, the procedure name, and the number of parameters do not uniquely identify an Oracle procedure. The UNIQUE ID is the value in the ALL_ARGUMENTS.OVERLOAD column in the Oracle system catalog. If you do not specify the UNIQUE ID parameter, the federated server detects the overloaded procedures and returns an error. Use this option only with Oracle procedures.
FOR SERVER ORA_SERVER: Required. Specifies a server definition where the federated procedure is created.
SPECIFIC MYPROC1: Specifies a unique name for the federated procedure that you are creating. This parameter is used only for federated procedures and is not associated with data source procedures. If you do not specify a unique name, a name is generated by the federated database manager. This parameter is optional.
WITH RETURN TO CLIENT ALL: Specifies that the result set is returned to the client application. Federation returns a maximum of one result set. If this parameter is not specified, the default is WITH RETURN TO CALLER ALL.
MODIFIES SQL DATA: Indicates the level of data access for SQL statements that are included in the federated procedure. If the clause specified does not match the Oracle procedure, an error message is returned. If you do not specify this clause, the clause for the Oracle procedure is used.
DETERMINISTIC: Specifies if the federated procedure always returns the same results for a given set of argument values. This parameter can improve the performance of the interaction between the federated server and the data source.
NO EXTERNAL ACTION: Specifies if the federated procedure takes an action that changes the state of an object that is not managed by the database manager.