| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Passthru and stored functions |
« View previous topic :: View next topic » |
| Author |
Message
|
| gisly |
 Posted: Wed Jun 27, 2012 4:46 am Post subject: Passthru and stored functions |
 |
|
Apprentice
Joined: 10 May 2012
Posts: 29
|
Hi!
I have got the following issue:
we have a stored procedure in an Oracle Database which has timestamp as an input parameter. We used to call it using "external", not "passhtru", and it worked, but it then turned out, that there was a problem with milliseconds dropped when data transferred to Oracle (the problem is also mentioned here :http://www.mqseries.net/phpBB2/viewtopic.php?t=55349&sid=6a61311af4c6e64b365d136ee744a907)
We don't want to mess with the stored function itself (as it was provided by another company), so we decided to use "Passhru". But I can't understand which syntax should be used:
if I use
| Code: |
set myRes=PASSTHRU('call function_name(
?,
?
)'
TO Database.SCHEMA
VALUES(arg1, arg2)) |
I get an error: Invalid function name (and I suppose it's connected with the absence of return value)
If is use
| Code: |
set myRes=PASSTHRU('select function_name(
?,
?
) from DUAL'
TO Database.SCHEMA
VALUES(arg1, arg2)) |
I get an error within the function itself (because inserts are used there, and they can't be executed within 'select')
I have tried lots of other options (to declare a variable and to call the function into it, to call the function using the schema prefix etc) but none worked.
Has anyone ever tried to use passthru with a stored function in Oracle?
Or should we try to find a different solution? |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Jun 27, 2012 5:45 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Your post is very confusing. You have twelve different thoughts all jumbled together. Try posting a reply that draws the focus to a single issue. Just reading your post makes my head hurt. Simplify.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Jun 27, 2012 7:28 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
I suspect that as it is a function rather than a procedure, Oracle would want you to use the {?=CALL(?,?)} ODBC escape syntax. However, one of the limitations of PASTHRU is that is cannot be used with OUT or INOUT parameters and I think this will also include the return value as well...
_________________
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 |
|
|
| kash3338 |
| Posted: Wed Jun 27, 2012 8:37 am Post subject: Re: Passthru and stored functions |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| gisly wrote: |
| Has anyone ever tried to use passthru with a stored function in Oracle? |
| Quote: |
The main use of the PASSTHRU statement is to issue administrative commands to databases (for example, to create a table).
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). |
http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fak05100_.htm
| gisly wrote: |
Or should we try to find a different solution? |
Yes
You can try changing the stored procedure or function (still not clear from your post on this). |
|
| Back to top |
|
|
| gisly |
| Posted: Wed Jun 27, 2012 9:34 pm Post subject: |
|
|
Apprentice
Joined: 10 May 2012
Posts: 29
|
The question is: has anyone used a stored FUNCTION (not a procedure) with PASSTHRU? If so, what is the syntax like? I have tried several variants (using just CALL, using SELECT FROM DUAL) and none worked.
As I guess from the replies, the answer is NO.
Thanks for your answers! |
|
| 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
|
|
|
|
