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 Index » WebSphere Message Broker (ACE) Support » Multiple Oracle SP out parameters

Post new topic  Reply to topic
 Multiple Oracle SP out parameters « View previous topic :: View next topic » 
Author Message
nelson
PostPosted: Wed Aug 26, 2015 12:12 pm    Post subject: Multiple Oracle SP out parameters Reply with quote

Partisan

Joined: 02 Oct 2012
Posts: 313
Does exist any workaround to allow an Oracle SP called from IIB9 to return more than one out parameters?

Getting this error:

Quote:
[Oracle][ODBC][Ora]Optional feature not implemented.


Thanks in advance.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Wed Aug 26, 2015 12:28 pm    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447
Optional feature not implemented with Oracle usually means you don't have the right Workaround value in the ODBC driver.

Otherwise, when a stored procedure call is returning more than one out parameter, you have to use dynamic results rather than specifying a fixed number.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
maurito
PostPosted: Wed Aug 26, 2015 11:28 pm    Post subject: Re: Multiple Oracle SP out parameters Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358
nelson wrote:
Does exist any workaround to allow an Oracle SP called from IIB9 to return more than one out parameters?

Getting this error:

Quote:
[Oracle][ODBC][Ora]Optional feature not implemented.


Thanks in advance.


There should be no problem returning more than 1 output parameter in Oracle.

mqjeff wrote:
Otherwise, when a stored procedure call is returning more than one out parameter, you have to use dynamic results rather than specifying a fixed number.

No, if the store procedure returns n out parameters, the ESQL signature should also contain n out parameters.
The signature of the ESQL definition for the store procedure MUST match the signature of the SP, otherwise the ODBC driver will not find it.
You use dynamic results when the SP returns result sets ( cursors in Oracle if I remember well). And for result sets, it can also return more than 1 result set.

The problem is likely to be an incorrect ODBC definition set up, unless you are mixing up OUT parameter with the RETURNS clause.

If you meant the RETURNS clause, then the answer is NO. This is what the doc says:
"The optional RETURNS clause is required if a stored procedure returns a single scalar value."
and I don't believe Oracle can RETURN multiple values
Back to top
View user's profile Send private message
mqjeff
PostPosted: Thu Aug 27, 2015 5:02 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447
In my experience "Optional feature not implemented" usually means that the Workarounds field on the Oracle odbc data source is not correct - http://www-01.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/bk58060_.htm

I do agree about OUT parameters, that was a mis-read of the original question on my part.

Dynamic Result sets, however, is a supported construction with databases and I have seen cases where it is used in Oracle (in fact, somewhat more often than other places).
http://www-01.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/bk58060_.htm

I'd be surprised if Oracle can't do that, as I thought that SQL allowed for a stored procedure to return a table or a list of values instead of a single return code.

But maybe I've not had my
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
maurito
PostPosted: Thu Aug 27, 2015 5:07 am    Post subject: Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358
mqjeff wrote:
In my experience "Optional feature not implemented" usually means that the Workarounds field on the Oracle odbc data source is not correct - http://www-01.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/bk58060_.htm

I do agree about OUT parameters, that was a mis-read of the original question on my part.

Dynamic Result sets, however, is a supported construction with databases and I have seen cases where it is used in Oracle (in fact, somewhat more often than other places).
http://www-01.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/bk58060_.htm

I'd be surprised if Oracle can't do that, as I thought that SQL allowed for a stored procedure to return a table or a list of values instead of a single return code.

But maybe I've not had my

Yes, I believe SQL allows to return a list, but it is not supported by IIB.
I have also seen the message for mismatching data types in the SQL statement.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Thu Aug 27, 2015 5:12 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447
Again, ESQL does allow for databases to return more than one result set.

Whether that's a LIST or not, I don't remember.

I do believe you're right that LIST is not a supported ESQL return time, but I won't swear to that off the top of my head.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
maurito
PostPosted: Thu Aug 27, 2015 5:28 am    Post subject: Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358
mqjeff wrote:
Again, ESQL does allow for databases to return more than one result set.

Whether that's a LIST or not, I don't remember.

I do believe you're right that LIST is not a supported ESQL return time, but I won't swear to that off the top of my head.

I think the confusion arises by the use of the word return which is also a CLAUSE in the CREATE FUNCTION/PROCEDURE
ESQL allows more than one result set in the DYNAMIC RESULT SETS clause, but it does not allow result sets/lists/rows in the RETURNS clause.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Multiple Oracle SP out parameters
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.