MQSeries.net :: View topic - Overload err when executing Oracle Stored Procedure

mqdevpsam · Posted: Sun Aug 06, 2006 1:41 pm Post subject:

WBIMB Ver - 5 CSD 5
Oracle 9.2
ODBC Driver type - Oracle in OraHome92, Ver - 9.02

I'm calling an Oracle stored procedure through a simple flow. In order to do that, I've define a local procedure which points towards the remote procedure (EXTERNAL NAME) and the node data source points to the correct remote database. The remote procedure exists and is reachable from the deployment location. An ODBC connection is correctly set and the mqsisetdbparms command is well executed. The account used owns the sufficient right to run the procedure. Moreover the parameters schema of the local procedure is exactly the same than the stored procedure.

The ESQL in the Compute Node is as follows -

DECLARE inputParm FLOAT;
DECLARE outputParm CHARACTER;

SET inputParm = CAST(Environment.Variables.AccountDetails.P_ID0 AS FLOAT);

CALL MB_GET_TOTAL(inputParm, outputParm);

CREATE PROCEDURE MB_GET_TOTAL ( IN parm1 FLOAT, OUT parm2 CHARACTER ) EXTERNAL NAME "PIN.GET_TOTAL";

Actually, when I try to process a message, the flow fails and the trace indicates that the call cannot be executed due overloaded versions procedures for a specific schema.

The exact err is as follows
----
( SA1BK_BROKER.default ) The database reports that the procedure 'PIN.GET_TOTAL' in schema 'PIN' has '2' overloaded versions. This is not supported, as it cannot be determined which of the '2' definitions is required.

If the procedure 'PIN.GET_TOTAL' is not already qualified with a schema name, qualification may remove this problem. Otherwise ensure that the procedure 'PIN.GET_TOTAL' is the only procedure of this name within schema 'PIN'
---------

However, the procedure isn't overloaded at all and I can't understand why such an error is raised. I am able to execute the Stored Proc from my Oracle standalone but not thru the msg flow.

Thanks in advance for your assistance.