ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportIn IIB10, getting Invalid SQL Statement error from Oracle

Post new topicReply to topic
In IIB10, getting Invalid SQL Statement error from Oracle View previous topic :: View next topic
Author Message
asrajesh
PostPosted: Wed Dec 19, 2018 1:38 am Post subject: In IIB10, getting Invalid SQL Statement error from Oracle Reply with quote

Novice

Joined: 19 Jan 2013
Posts: 18

Recently, we have migrated from IIB 9 to IIB 10.0.0.14. We had one service (done in IIB9) which calls Oracle SP that returns Ref cursor in the output parameter.

The service was working fine in IIB 9. But, after migrating to IIB 10, the same service is returning

Code:
[IBM][ODBC 20101 driver][Oracle]ORA-00900: invalid SQL Statement


when procedure returning No Data found error. The services works fine if the data found for the given parameter.

The Oracle SP signature is



Code:
CREATE OR REPLACE procedure CONSOLIDATE_EXCH_RATE(
    Exchange_Dt      IN  varchar2,
    ALPHAISOCODE     IN  varchar2,
    Error_Code       OUT varchar2,
    Error_Desc       OUT varchar2,
    ORA_ERROR_CODE   OUT varchar2,
    ORA_ERROR_DESC   OUT varchar2,
    ExchangeRateDtls OUT sys_refcursor)


And from IIB service, we call this SP as folows

Code:
CALL CONSOLIDATE_EXCH_RATE(
    COALESCE(ExchangeDt,''),
    COALESCE(AlphaCurCode,''),
    Environment.Variables.ErrorCode,
    Environment.Variables.ErrorDesc,
    Environment.Variables.ORAErrorCode,
    Environment.Variables.ORAErrorDesc,
    Environment.Variables.ExchangeRateDtls[]
CREATE PROCEDURE CONSOLIDATE_EXCH_RATE (
    IN  EXCHANGE_DT         CHARACTER,
    IN  ISO_CURCODE         CHARACTER,
    OUT ErrorCode           CHARACTER,
    OUT ErrorDesc           CHARACTER,
    OUT ORAERRORCODE        CHARACTER,
    OUT ORAERRORDESC        CHARACTER)
    DYNAMIC RESULT SETS 1
       EXTERNAL NAME "%.CONSOLIDATE_EXCH_RATE" ;


Can you please help us to rectify this issue ?
Back to top
View user's profile Send private message
gfrench
PostPosted: Fri Dec 21, 2018 1:48 am Post subject: Reply with quote

Acolyte

Joined: 10 Feb 2002
Posts: 68

This will probably be related to the ODBC driver. Did you migrate the ODBC to use the v10 driver? If so what platform are you on and did you set the properties the same as they were with v9. I think/maybe there is an option for allowing it to return a result set..... "procedure returns results" on windows
Back to top
View user's profile Send private message Send e-mail Visit poster's website
asrajesh
PostPosted: Sat Dec 22, 2018 10:14 pm Post subject: Reply with quote

Novice

Joined: 19 Jan 2013
Posts: 18

Thanks gfrench for your reply

We are running on HP Unix Platform.

Yes .. we have migrated ODBC driver to use the V10 driver. Please find below the ODBC setting for this DB


Code:
[ODSDB]
Driver=/opt/IBM/mqsi/10.0.0.14/iib-10.0.0.14/server/ODBC/drivers/lib/UKora95.so
Description=DataDirect 6.0 Oracle Wire Protocol
#HostName=<Your Oracle Server Machine Name>
#PortNumber=<Port on which Oracle is listening on HostName>
ServerName=CPDB_SYSTEST
CatalogOptions=0
EnableStaticCursorsForLongData=1
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
DefaultLongDataBuffLen=8192
LoginTimeout=10
QueryTimeout=30
Back to top
View user's profile Send private message
asrajesh
PostPosted: Sat Dec 29, 2018 5:25 am Post subject: Reply with quote

Novice

Joined: 19 Jan 2013
Posts: 18

Somebody, please help us in this issue as it is affecting our environment badly
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportIn IIB10, getting Invalid SQL Statement error from Oracle
Jump to:



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.