| Author |
Message
|
| umaneesh |
 Posted: Sun Apr 11, 2010 10:20 am Post subject: retrieving REF CURSOR from DB using ESQL |
 |
|
Novice
Joined: 13 Apr 2009
Posts: 24
|
Hi,
I have a requirement to call an oracle stored procedure which has REF_CURSOR parameter included. I have to retrieve the ref_cursor [result set]to get the values back from DB. When I do this from ESQL, I am receiving Invalid parameter error back from DB.
Stored proc in oracle:
PROCEDURE ORC_SP_DATA (
SP_ID IN VARCHAR2,
IN SP_CODE IN VARCHAR2,
AHG_CUR OUT REF CURSOR);
ESQL code to call:
CREATE PROCEDURE UPDATE_RETR_SP(IN SP_ID CHARACTER, IN SP_CODE CHARACTER) LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "QUAL.SP_ORC.ORC_SP_DATA";
| Quote: |
| CALL UPDATE_RETR_SP(sp_Id, sp_code, Environment.ResultSet1[]); |
Is this the way should I call ref_cursor parameters from ESQL? Am I doing something wrong?
pls 
Thanks! |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Sun Apr 11, 2010 12:41 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
I would have thought that you'd have to put the result set into a variable of type ROW... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mgk |
| Posted: Sun Apr 11, 2010 2:15 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| I would have thought that you'd have to put the result set into a variable of type ROW |
He did (effectively) as ROW is just another word for tree...
The syntax used here looks correct, so can you post the full text of the error please along with the versions of Broker and Oracle. Also check that you have enabled the driver option "ProcedureReturnsResults"...
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| umaneesh |
| Posted: Sun Apr 11, 2010 5:02 pm Post subject: |
|
|
Novice
Joined: 13 Apr 2009
Posts: 24
|
| mgk wrote: |
| Quote: |
| I would have thought that you'd have to put the result set into a variable of type ROW |
He did (effectively) as ROW is just another word for tree... |
Yes. This is not working either.
| mgk wrote: |
The syntax used here looks correct, so can you post the full text of the error please along with the versions of Broker and Oracle. |
Oracle version: 10.2.0-64
Broker version: 6.1.0.5
Error text:
| Quote: |
Apr 11 19:38:18 user:err|error WebSphere Broker v6105[1249416]: (BROKER.EXECGRP)[2964]BIP2321E: Database error: ODBC return code '-1'. : BROKER.3395ac57-2601-0000-0080-d742dba13ec8: /build/S610_P/src/DataFlowEngine/ImbOdbc.cpp: 333: ImbOdbcHandle::checkRcInner: :
Apr 11 19:38:18 user:err|error WebSphere Broker v6105[1249416]: (BROKER.EXECGRP)[2964]BIP2322E: Database error: SQL State 'HY000'; Native Error Code '20103'; Error Text '[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-20103: Invalid param - SP_CODE is invalid ORA-06512: at "QUAL.SP_ORC.ORC_SP_DATA", line 149 ORA-06512: |
| mgk wrote: |
| Also check that you have enabled the driver option "ProcedureReturnsResults"... |
|
|
| Back to top |
|
|
| umaneesh |
| Posted: Sun Apr 11, 2010 6:16 pm Post subject: |
|
|
Novice
Joined: 13 Apr 2009
Posts: 24
|
In addition to my above response,
| mgk wrote: |
| Also check that you have enabled the driver option "ProcedureReturnsResults"... |
[/quote]
I am restricted to see the options. But will get it shortly.
I have observed one thing. When I executed the same stored proc from SQL Plus, it worked. That means, the issue is with the way I call from ESQL. Thats for sure.
Will this work by DatabaseRetrieve Node or Mapping node? I guess, when this doesnt work with esql the same way with these nodes too.
Thanks. |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Sun Apr 11, 2010 10:04 pm Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| I may be missing the mark here as its been a while since i've played with result sets and broker, but..... in this instance, shouldnt you declare an extra out parameter on the procedure declaration in ESQL of type reference and then remove the dynamic result set declaration bit? |
|
| Back to top |
|
|
| umaneesh |
| Posted: Wed Apr 14, 2010 8:24 am Post subject: |
|
|
Novice
Joined: 13 Apr 2009
Posts: 24
|
nothing worked out. Infact we happened to see this in info center of v6:
| Quote: |
| Parameters cannot be of the ESQL REFERENCE, ROW, LIST, or INTERVAL data types. |
We had changed the stored procedure to have a CLOB type instead of REF_CURSOR now.
But, I dont understand how it worked here in the below thread!
http://www.mqseries.net/phpBB2/viewtopic.php?t=52871&sid=dcc33bbee34fccff8f3c1546705685b4
Thanks |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Apr 14, 2010 10:00 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi, please raise a PMR for this as this does work and is supported. There must be something different in your environment which is preventing this from working in this case. The documentation you found is regarding defined parameters on the function itself, rather than DYNAMIC RESULT SETS
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| akmar2k |
| Posted: Tue May 24, 2011 8:17 am Post subject: Resolution? |
|
|
Novice
Joined: 01 Jun 2006
Posts: 22
|
Hello,
Has there been any resolution on this issue? Did you run it with IBM and got any responses?
Thanks |
|
| Back to top |
|
|
| umaneesh |
| Posted: Tue May 24, 2011 12:12 pm Post subject: Resolution? |
|
|
Novice
Joined: 13 Apr 2009
Posts: 24
|
Hi,
I remember the issue was with Oracle stored procedure. It started working fine when DB guys fixed it. But broker supports ref_cursor and we captured it like this,
CALL UPDATE_RETR_SP(sp_Id, sp_code, Environment.ResultSet1[]);
Thanks |
|
| Back to top |
|
|
|
|
