Overloaded procedures in federated systems

Overloaded procedures are procedures that have identical names and schemas. Overloaded procedures can have a different number of parameters or different parameter signatures. The purpose of overloading a procedure is to create similar versions of a procedure.

The federated server allows overloaded procedures only if each procedure has a different number of parameters.

Oracle allows overloaded procedures if each procedure has a different number of parameters or if the parameter types are different. You can create federated procedures for Oracle overloaded procedures.

To distinguish procedures that use the identical name, schema name and number of parameters, you must specify the UNIQUE ID when you create the federated procedure.

You can determine the UNIQUE ID by querying the Oracle catalog.

Query the Oracle catalog

To determine the UNIQUE ID, you can query the OVERLOAD column in the Oracle SYS.ALL_ARGUMENTS catalog. The UNIQUE ID value is a character literal that contains a number, such as 1. You can use the passthru mode on the federated server or query the Oracle client directly.

For example, to query the Oracle catalog and display the signature and overload column for a procedure that begins with HJZ, use the following SELECT statement:

SELECT owner, package_name, object_name, overload, position, argument_name, in_out, 
data_type
   FROM all_arguments aa
   WHERE object_name like 'HJZ%'
   ORDER BY owner, package_name, object_name,overload, position;

The following output from the above query shows that the HJZ_PACK1 package contains three procedures that use the name HJZTEST1. You determine the number of procedures by looking at the OBJECT_NAME AND OVERLOAD columns. The first procedure has one IN parameter with a number data type. The second procedure has one IN parameter with a character data type. The third procedure has one OUT parameter with a character data type and one IN parameter with a number data type. The output shows that there are two procedures in the HJZ_PACK1 package that use the name HJZTEST3. There is also a procedure with the name HJZTEST1 that is not in the package. This last procedure has one IN parameter that uses a number data type.

OWNER    PACKAGE_NAME OBJECT_NAME OVERLOAD POSITION ARGUMENT_NAME IN_OUT DATA_TYPE
-------- ------------ ----------- -------- -------- ------------- ------ ---------
J15USER1 HJZ_PACK1    HJZTEST1    1        1        A             IN     NUMBER
J15USER1 HJZ_PACK1    HJZTEST1    2        1        A             IN     CHAR
J15USER1 HJZ_PACK1    HJZTEST1    3        0        -             OUT    CHAR
J15USER1 HJZ_PACK1    HJZTEST1    3        1        A             IN     NUMBER
J15USER1 HJZ_PACK1    HJZTEST3    1        1        A             IN     NUMBER
J15USER1 HJZ_PACK1    HJZTEST3    1        2        B             OUT    NUMBER
J15USER1 HJZ_PACK1    HJZTEST3    2        1        A             IN     CHAR
J15USER1 HJZ_PACK1    HJZTEST3    2        2        B             OUT    CHAR
J15USER1 -            HJZTEST1    -        1        A             IN     NUMBER
9 record(s) selected.

To create a federated procedure for the second overloaded procedure with an IN parameter of CHAR data type, issue the following CREATE PROCEDURE statement:

CREATE PROCEDURE HJZTEST1 SOURCE J15USER1.HJZ_PACK1.HJZTEST1
	NUMBER OF PARAMETERS 1 UNIQUE ID '2' 
	FOR SERVER ORA_SERVER WITH RETURN TO CLIENT ALL;

Important: In the above example, the NUMBER OF PARAMETERS clause does not uniquely identify the procedure. There are two procedures in the table with the name HJZTEST1 and each procedure has one parameter. You must specify the UNIQUE ID clause to indicate the overloaded procedure that you want to use. Use the value from the OVERLOAD column as the value for the UNIQUE_ID clause. When the UNIQUE ID clause is specified, the NUMBER OF PARAMETERS clause is optional. Use the NUMBER OF PARAMETERS clause to validate that the data source procedure has the number of parameters that you expect.