MQSeries.net :: View topic - Problem passing parms in Oracle Stored Proc

Robert · Posted: Wed Jan 14, 2004 2:58 am Post subject:

Hi,

I am experimenting problems passing parameters to a Stored Procedure in Oracle 9.2.03 from an ESQL in a Database Node in MB V5.0 CSD02. I tried the same code in DB/2 8.1 and it works fine.

I only have one version of the stored proc in the entire Oracle database SID.

Here is the ESQL Code Snipet:

BROKER SCHEMA HR

CREATE COMPUTE MODULE testHR_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
-- CALL CopyMessageHeaders();
-- CALL CopyEntireMessage();
RETURN TRUE;
END;

CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;

CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;
END MODULE;

CREATE DATABASE MODULE testHR_Database
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN

DECLARE reservNo CHARACTER;
DECLARE extSTName CHARACTER;

SET reservNo = '100';

call deletePassengerSTP (reservNo);

RETURN TRUE;
END;

END MODULE;

--define the external database stored procedure

CREATE PROCEDURE DeletePassengerSTP (
IN reservNo CHARACTER) EXTERNAL NAME "HR.ORADELETEPASSENGER1";

Here is the actual Oracle Stored Proc:

CREATE OR REPLACE PROCEDURE "HR"."ORADELETEPASSENGER1" (reservNo
IN VARCHAR2)
AS
BEGIN
DELETE FROM PASSENGERTB WHERE RESERVATIONNO = reservNo;
END;

Here is the Error Message from the NT Application Log:

Event Type: Error
Event Source: BIPv500
Event Category: None
Event ID: 2922
Date: 1/13/2004
Time: 10:04:05 PM
User: N/A
Computer: PELLET99
Description:
( BRK01.default ) The database reports that the procedure 'HR.ORADELETEPASSENGER1' in schema 'HR' has '2' overloaded versions. This is not supported, as it cannot be determined which of the '2' definitions is required.

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

Correct this problem and redeploy the messageflow.
Data:
0000: 33 00 34 00 38 00 30 00 3.4.8.0.
0008: 00 00 42 00 52 00 4b 00 ..B.R.K.
0010: 30 00 31 00 2e 00 64 00 0.1...d.
0018: 66 00 35 00 38 00 66 00 f.5.8.f.
0020: 30 00 36 00 36 00 2d 00 0.6.6.-.
0028: 66 00 39 00 30 00 30 00 f.9.0.0.
0030: 2d 00 30 00 30 00 30 00 -.0.0.0.
0038: 30 00 2d 00 30 00 30 00 0.-.0.0.
0040: 38 00 30 00 2d 00 38 00 8.0.-.8.
0048: 64 00 37 00 64 00 34 00 d.7.d.4.
0050: 39 00 39 00 62 00 32 00 9.9.b.2.
0058: 64 00 30 00 33 00 00 00 d.0.3...
0060: 46 00 3a 00 5c 00 62 00 F.:.\.b.
0068: 75 00 69 00 6c 00 64 00 u.i.l.d.
0070: 5c 00 53 00 30 00 30 00 \.S.0.0.
0078: 30 00 5f 00 50 00 5c 00 0._.P.\.
0080: 73 00 72 00 63 00 5c 00 s.r.c.\.
0088: 44 00 61 00 74 00 61 00 D.a.t.a.
0090: 46 00 6c 00 6f 00 77 00 F.l.o.w.
0098: 45 00 6e 00 67 00 69 00 E.n.g.i.
00a0: 6e 00 65 00 5c 00 49 00 n.e.\.I.
00a8: 6d 00 62 00 52 00 64 00 m.b.R.d.
00b0: 6c 00 5c 00 49 00 6d 00 l.\.I.m.
00b8: 62 00 52 00 64 00 6c 00 b.R.d.l.
00c0: 52 00 6f 00 75 00 74 00 R.o.u.t.
00c8: 69 00 6e 00 65 00 2e 00 i.n.e...
00d0: 63 00 70 00 70 00 00 00 c.p.p...
00d8: 31 00 30 00 38 00 34 00 1.0.8.4.
00e0: 00 00 45 00 6e 00 73 00 ..E.n.s.
00e8: 75 00 72 00 65 00 4e 00 u.r.e.N.
00f0: 6f 00 4f 00 76 00 65 00 o.O.v.e.
00f8: 72 00 6c 00 6f 00 61 00 r.l.o.a.
0100: 64 00 65 00 64 00 50 00 d.e.d.P.
0108: 72 00 6f 00 63 00 65 00 r.o.c.e.
0110: 64 00 75 00 72 00 65 00 d.u.r.e.
0118: 73 00 3a 00 3a 00 6f 00 s.:.:.o.
0120: 70 00 00 00 00 00 p.....

Anybody has a clue on how to solve this problem?

Thanks.

Robert
[/b]