MQSeries.net :: View topic - ESQL Calling Oracle 11g Stored Procedure error

mdmader · Posted: Thu Oct 23, 2008 8:21 am Post subject:

I've run into a small problem trying to call an Oracle 11g Stored Procedure from ESQL.

This procedure is a simple security verification for an account and service request. The ESQL triggers the error [DataDirect][ODBC Oracle driver]Optional feature not implemented. when it calls the stored procedure.

Actual Error:
DatabaseException
File:CHARACTER:F:\build\S610_P\src\DataFlowEngine\ImbOdbc.cpp
Line:INTEGER:359
Function:CHARACTER:ImbOdbcHandle::checkRcInner
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPv610
Severity:INTEGER:3
Number:INTEGER:2322
Text:CHARACTER:Child SQL exception
Insert
Type:INTEGER:5
Text:CHARACTER:HYC00
Insert
Type:INTEGER:2
Text:CHARACTER:0
Insert
Type:INTEGER:5
Text:CHARACTER:[DataDirect][ODBC Oracle driver]Optional feature not implemented.

I've searched around on exception but I have not anything that seem relevant. Searched on the Description and the HYC00 code. The ODBC Driver I'm using is "MQSeries DataDirect Techonlogies 5.2 32-Bit Oracle Version 5.20.00.67. Message Broker Version 6.1.0.1, Oracle 9i client 9.2.0.4.0 and Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit.

Every time I try to enable ODBC tracing on my machine and restart Broker, the message flow stays in a stopped state and will not run. If I remove ODBC trace, the flow will again run. Again, if anyone had this problem, please enlighten me.

Has anyone encountered this and know what the error is referring to and if they know what a solution may be.

Any help/pointers/ideas would be appreciated.

ESQL:
CREATE COMPUTE MODULE VerifyRequest
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
SET OutputRoot = InputRoot;
SET OutputLocalEnvironment = InputLocalEnvironment;

DECLARE serviceName CHAR;
DECLARE subscriberName CHAR;
DECLARE validRequest CHAR;
DECLARE valid BOOLEAN;

SET serviceName = InputRoot.MRM.V0_5_SERVICE_NAME;
SET subscriberName = InputRoot.MRM.V0_5_SUBSCRIBERNAME;

call verify(serviceName, subscriberName, valid);

IF (valid <> TRUE) THEN
-- ERROR, Not Valid
SET Environment.Variables.EH.KeyData = 'Verification Exception';
SET Environment.Variables.EH.messageText = 'Invalid Request.';
THROW EXCEPTION;
END IF;

RETURN TRUE;
END;

CREATE PROCEDURE verify(IN p_ServiceName CHAR, IN p_SubscriberName CHAR, INOUT p_Valid BOOLEAN) LANGUAGE DATABASE EXTERNAL NAME "EDSAPP_MGR.PR_EDS_SERVICE.pr_Validate_Service_Call";
END MODULE;

Procedure:
PROCEDURE pr_Validate_Service_Call(p_ServiceName IN VARCHAR2,
p_SubscriberName IN VARCHAR2,
p_Valid IN OUT BOOLEAN)
AS
BEGIN
v_service_found := 0;
SELECT 1
INTO v_service_found
FROM eds_subscription
WHERE svc_subscriber_name = p_subscribername
AND eds_service_name = p_servicename;

if v_service_found = 1 then
p_Valid := TRUE;
else
p_Valid := FALSE;
end if;

EXCEPTION
WHEN NO_DATA_FOUND THEN
p_Valid := FALSE;
WHEN OTHERS THEN
RAISE;
END pr_Validate_Service_Call;