MQSeries.net :: View topic

brokerDev · Posted: Tue Jan 30, 2007 6:02 am Post subject:

Hi,

I am trying to call an oracle stored procedure with no success. I am using debug and as soon as I enter the node. I get a DatabaseException "Root SQL exception"

The ESQL is -

SET GeometryData = .................;
MOVE inRef FIRSTCHILD NAMESPACE * NAME 'SecurityStructure';

CALL myRegisterAccess (
inRef.{securityNS}:GASPIN,
inRef.{securityNS}:SPNUMBER,
'XXX',
1234,
CURRENT_DATE,
GeometryData,
transactionID);

CREATE PROCEDURE myRegisterAccess (
IN my_gaspin CHAR,
IN my_spNumber CHAR,
IN my_targetFormat CHAR,
IN my_blueskyLayerId INT,
IN my_dateOfInterest DATE,
IN my_geometryData CHAR,
OUT my_transactionId INT)
EXTERNAL NAME "registerAccess";

The Oracle stored proc has been defined as -

CREATE OR REPLACE PROCEDURE registerAccess
( p_gaspin IN SC_USER.GASPIN%TYPE DEFAULT NULL
,p_spNumber IN SC_USER.SP_ID%TYPE DEFAULT NULL
,p_targetFormat IN WF_LOG_MSG_FME.TARGET_FORMAT%TYPE
,p_blueskyLayerId IN MG_LAYERS.BLUESKY_LAYER_ID%TYPE
,p_dateOfInterest IN DATE DEFAULT NULL
,p_geometryData IN CLOB DEFAULT NULL
,p_transactionId OUT WF_LOG_MSG_FME.ID_WF_LOG_MSG%TYPE )

IS
BEGIN
p_transactionId := 66;
END registerAccess;
/

I can access the database ok and I am therefore satisfied that odbc connections etc are fine. Here is the bit of ESQL I used to test DB connectivity -

DECLARE jaRef reference to OutputLocalEnvironment;
SET jaRef.mine[] = (Select XXX.MESSAGE
From Database.WF_LOG_MSG as XXX
Where XXX.ID = 66
);

Does anyone have any suggestions as to why I am getting the database exception? I think the problem is with the stored proc...not sure how I check that though...

Thanks