| Author |
Message
|
| kash3338 |
 Posted: Tue Sep 06, 2011 8:38 am Post subject: ResultSet from Oracle |
 |
|
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 |
|
 |
| Gaya3 |
| Posted: Tue Sep 06, 2011 8:44 am Post subject: |
|
|
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 |
|
|
| kash3338 |
| Posted: Tue Sep 06, 2011 9:06 am Post subject: |
|
|
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 |
|
|
| Vitor |
| Posted: Tue Sep 06, 2011 9:09 am Post subject: |
|
|
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 |
|
|
| kash3338 |
| Posted: Tue Sep 06, 2011 9:12 am Post subject: |
|
|
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 |
|
|
| lancelotlinc |
| Posted: Tue Sep 06, 2011 9:39 am Post subject: |
|
|
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 |
|
|
| Vitor |
| Posted: Tue Sep 06, 2011 10:34 am Post subject: |
|
|
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 |
|
|
| rekarm01 |
| Posted: Tue Sep 06, 2011 11:04 pm Post subject: Re: ResultSet from Oracle |
|
|
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 |
|
|
| kash3338 |
| Posted: Wed Sep 07, 2011 10:16 pm Post subject: |
|
|
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 |
|
|
| rekarm01 |
| Posted: Thu Sep 08, 2011 1:25 am Post subject: Re: ResultSet from Oracle |
|
|
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?
|
|
| Back to top |
|
|
| kash3338 |
| Posted: Thu Sep 08, 2011 2:09 am Post subject: Re: ResultSet from Oracle |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| rekarm01 wrote: |
Did this help?
|
Sorry for the wrong quote.
Yes it did help, thanks for that link. We were looking for something like that. |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Thu Sep 08, 2011 7:24 am Post subject: Re: ResultSet from Oracle |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
| kash3338 wrote: |
| rekarm01 wrote: |
Did this help?
|
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 |
|
|
| rekarm01 |
| Posted: Thu Sep 08, 2011 11:21 am Post subject: Re: ResultSet from Oracle |
|
|
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 |
|
|
| Gaya3 |
| Posted: Thu Sep 08, 2011 11:31 am Post subject: Re: ResultSet from Oracle |
|
|
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 |
|
|
| rekarm01 |
| Posted: Thu Sep 08, 2011 1:18 pm Post subject: Re: ResultSet from Oracle |
|
|
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 |
|
|
|
|
