| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| In IIB10, getting Invalid SQL Statement error from Oracle |
« View previous topic :: View next topic » |
| Author |
Message
|
| asrajesh |
 Posted: Wed Dec 19, 2018 1:38 am Post subject: In IIB10, getting Invalid SQL Statement error from Oracle |
 |
|
Novice
Joined: 19 Jan 2013
Posts: 20
|
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 |
|
 |
| gfrench |
| Posted: Fri Dec 21, 2018 1:48 am Post subject: |
|
|
Acolyte
Joined: 10 Feb 2002
Posts: 71
|
| 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 |
|
|
| asrajesh |
| Posted: Sat Dec 22, 2018 10:14 pm Post subject: |
|
|
Novice
Joined: 19 Jan 2013
Posts: 20
|
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 |
|
|
| asrajesh |
| Posted: Sat Dec 29, 2018 5:25 am Post subject: |
|
|
Novice
Joined: 19 Jan 2013
Posts: 20
|
| Somebody, please help us in this issue as it is affecting our environment badly |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
