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 » ResultSet from Oracle

Post new topic  Reply to topic Goto page 1, 2  Next
 ResultSet from Oracle « View previous topic :: View next topic » 
Author Message
kash3338
PostPosted: Tue Sep 06, 2011 8:38 am    Post subject: ResultSet from Oracle Reply with quote

Shaman

Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India

Hi,

We invoke a PL/SQL procedure from our ESQL code. The PL/SQL procedure returns a ResultSet, Is there a way to get this ResultSet in ESQL? If so, how to get that?
Back to top
View user's profile Send private message Send e-mail
Gaya3
PostPosted: Tue Sep 06, 2011 8:44 am    Post subject: Reply with quote

Jedi

Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US

yes, use PASSTHRU function to do this
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die
Back to top
View user's profile Send private message
kash3338
PostPosted: Tue Sep 06, 2011 9:06 am    Post subject: Reply with quote

Shaman

Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India

Gaya3 wrote:
yes, use PASSTHRU function to do this


I have a Procedure created in my ESQL to invoke the PL/SQL procedure. The PL/SQL returns a RECORDTYPE and how do I get that in my ESQL? I am not using a single line statement but invoking a PL/SQL Procedure.
Back to top
View user's profile Send private message Send e-mail
Vitor
PostPosted: Tue Sep 06, 2011 9:09 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

kash3338 wrote:
The PL/SQL returns a RECORDTYPE and how do I get that in my ESQL?


By using the PASSTHRU function.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
kash3338
PostPosted: Tue Sep 06, 2011 9:12 am    Post subject: Reply with quote

Shaman

Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India

Vitor wrote:

By using the PASSTHRU function.


Can you please give me the syntax or any links which explains this?
Back to top
View user's profile Send private message Send e-mail
lancelotlinc
PostPosted: Tue Sep 06, 2011 9:39 am    Post subject: Reply with quote

Jedi Knight

Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA

kash3338 wrote:
Vitor wrote:

By using the PASSTHRU function.


Can you please give me the syntax or any links which explains this?


You can get these in the IBM-supplied training. Have you taken the training yet? If not, why not?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER
Back to top
View user's profile Send private message Send e-mail
Vitor
PostPosted: Tue Sep 06, 2011 10:34 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

kash3338 wrote:
Vitor wrote:

By using the PASSTHRU function.


Can you please give me the syntax or any links which explains this?


How about the entry for the PASSTHRU function in the InfoCenter?
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
rekarm01
PostPosted: Tue Sep 06, 2011 11:04 pm    Post subject: Re: ResultSet from Oracle Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 1415

Gaya3 wrote:
yes, use PASSTHRU function to do this

PASSTHRU may not be necessary here. ESQL supports declaring external procedures that return dynamic result sets.
Back to top
View user's profile Send private message
kash3338
PostPosted: Wed Sep 07, 2011 10:16 pm    Post subject: Reply with quote

Shaman

Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India

Vitor wrote:
You can get these in the IBM-supplied training. Have you taken the training yet? If not, why not?


As far as i know, PASSTHRU is for executing some complex SELECT queries and not to invoke external Procedure's. Here my requirement is to invoke a existing PL/SQL Procedure which returns a RESULTSET.
Back to top
View user's profile Send private message Send e-mail
rekarm01
PostPosted: Thu Sep 08, 2011 1:25 am    Post subject: Re: ResultSet from Oracle Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 1415

kash3338 wrote:
quote="Vitor"]You can get these in the IBM-supplied training. Have you taken the training yet? If not, why not?

"Vitor"? Check again.

kash3338 wrote:
Here my requirement is to invoke a existing PL/SQL Procedure which returns a RESULTSET.

Did this help?
rekarm01 wrote:
ESQL supports declaring external procedures that return dynamic result sets.
Back to top
View user's profile Send private message
kash3338
PostPosted: Thu Sep 08, 2011 2:09 am    Post subject: Re: ResultSet from Oracle Reply with quote

Shaman

Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India

rekarm01 wrote:

Did this help?
rekarm01 wrote:
ESQL supports declaring external procedures that return dynamic result sets.


Sorry for the wrong quote.

Yes it did help, thanks for that link. We were looking for something like that.
Back to top
View user's profile Send private message Send e-mail
Gaya3
PostPosted: Thu Sep 08, 2011 7:24 am    Post subject: Re: ResultSet from Oracle Reply with quote

Jedi

Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US

kash3338 wrote:
rekarm01 wrote:

Did this help?
rekarm01 wrote:
ESQL supports declaring external procedures that return dynamic result sets.


Sorry for the wrong quote.

Yes it did help, thanks for that link. We were looking for something like that.


did you try using PASSTHRU and tested. i suggest you to give a try first and see if it works, if it doesnt let us think over it.

Eg: PASSTHRU('CALL ABC()')
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die
Back to top
View user's profile Send private message
rekarm01
PostPosted: Thu Sep 08, 2011 11:21 am    Post subject: Re: ResultSet from Oracle Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 1415

Gaya3 wrote:
did you try using PASSTHRU and tested. i suggest you to give a try first and see if it works, if it doesnt let us think over it.

Eg: PASSTHRU('CALL ABC()')

Even if PASSTHRU works, the documentation advises against it:
Quote:
Note: Do not use PASSTHRU to call stored procedures; instead, use the CALL statement because PASSTHRU imposes limitations (you cannot use output parameters, for example).

ESQL supports declaring external procedures that return dynamic result sets, so PASSTHRU shouldn't be necessary.
Back to top
View user's profile Send private message
Gaya3
PostPosted: Thu Sep 08, 2011 11:31 am    Post subject: Re: ResultSet from Oracle Reply with quote

Jedi

Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US

rekarm01 wrote:
Gaya3 wrote:
did you try using PASSTHRU and tested. i suggest you to give a try first and see if it works, if it doesnt let us think over it.

Eg: PASSTHRU('CALL ABC()')

Even if PASSTHRU works, the documentation advises against it:
Quote:
Note: Do not use PASSTHRU to call stored procedures; instead, use the CALL statement because PASSTHRU imposes limitations (you cannot use output parameters, for example).

ESQL supports declaring external procedures that return dynamic result sets, so PASSTHRU shouldn't be necessary.


yes..i know..this is infact a debatable topic,
ESQL:
it is easy to use CREATE PROCEDURE

Java:
i believe CreateSQLStatement uses PASSTHRU function internally.

Here comes the question, is it good or not. and it says imposes limitations, it depends upon the business logic though. How we need to achieve the results.
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die
Back to top
View user's profile Send private message
rekarm01
PostPosted: Thu Sep 08, 2011 1:18 pm    Post subject: Re: ResultSet from Oracle Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 1415

Gaya3 wrote:
rekarm01 wrote:
Quote:
Note: Do not use PASSTHRU to call stored procedures; instead, use the CALL statement because PASSTHRU imposes limitations (you cannot use output parameters, for example).

ESQL supports declaring external procedures that return dynamic result sets, so PASSTHRU shouldn't be necessary.

yes..i know..this is infact a debatable topic, ... it depends upon the business logic though. How we need to achieve the results.

Under what circumstances should a message flow use the PASSTHRU statement instead of a CALL statement, in order to call a stored procedure?

Gaya3 wrote:
Java: i believe CreateSQLStatement uses PASSTHRU function internally.

What is the basis of this belief?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Goto page 1, 2  Next Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » ResultSet 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.