| Author |
Message
|
| jlee |
 Posted: Thu May 03, 2007 9:11 pm Post subject: Zero Iteration Count Err when I call Oracle Stored Procedure |
 |
|
Newbie
Joined: 03 May 2007
Posts: 7
Location: Auckland, NZ
|
Hi there,
Have a problem that I need to sort out for a project that I'm on.
I am getting the following error message when I execute an Oracle stored procedure that I am using.
| Quote: |
MESSAGE-NO: BIP2322: PROBLEM-DESCRIPTION->Child SQL exception
[DataDirect][ODBC Oracle driver][Oracle]ORA-24333: zero iteration count
Error occured in procedure->Get_Members_For_Fulfilment
|
The funny thing is, is that I run the stored procedure using an external tool with the exact same parameters and it works fine. However with the message flow that I am using it does not. I had a feeling that it might have been to do if no results were returned, but it also happens when I'm executing the procedure with parameters that do return results.
I have provided the code that I am calling below. Any help would be greatly appreciated.
| Code: |
CREATE PROCEDURE Get_Members_For_Fulfilment
(
IN date_from CHARACTER,
IN date_to CHARACTER,
IN run_type CHARACTER,
OUT error_code CHARACTER,
OUT error_msg CHARACTER
)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "ALP.koru_inbound_fulfilment_pkg.get_members_for_fulfilment";
|
| Code: |
CALL Get_Members_For_Fulfilment('04042005',
'05052006',
'LIFE',
code,
msg,
OutputRoot.XML.LifeMembers.Member[]);
|
I have an extract of the ODBC.ini file too which may be useful.
| Code: |
[ALPD]
Driver=/opt/mqsi/merant/lib/UKor820.so
Description=DataDirect 5.0 Oracle
EnableDescribeParam=1
OptimizePrepare=1
ServerName=alpd.airnz.co.nz
WorkArounds=536870912
ProcedureRetResults=1
|
Cheers,
Jeremy |
|
| Back to top |
|
 |
| sparsons |
| Posted: Fri May 11, 2007 12:23 pm Post subject: I too have encoutered this problem |
|
|
Newbie
Joined: 11 Mar 2002
Posts: 6
|
| Please post a solution if you have one. |
|
| Back to top |
|
|
| marcin.kasinski |
| Posted: Fri May 11, 2007 12:33 pm Post subject: Re: I too have encoutered this problem |
|
|
Sentinel
Joined: 21 Dec 2004
Posts: 850
Location: Poland / Warsaw
|
|
| Back to top |
|
|
| jlee |
| Posted: Fri May 11, 2007 3:43 pm Post subject: |
|
|
Newbie
Joined: 03 May 2007
Posts: 7
Location: Auckland, NZ
|
Hi,
Unfortunately we were not able to determine the exact cause of this error.
On our end, we had 2 other Oracle stored procedures that returned cursors and are having no difficulty with them. We are able to call the problematic stored procedure once, but when we call it mutliple times in a row we get the zero iteration count error.
We then created a simple flow which, upon receiving a abitrary input message calls the stored procedure. Multiple messages sent allow the stored procedure to be called multiple times.
The stored procedures were created by an external contractor who has since reviewed his stored procedures and is adamant that they are correct and operational.
The time constraint placed on this project, and the amount of time I tried to debug the problem, meant that I ended up resorting to a different design altogether. I hate when I have problems that I can't resolve - very frustrating!
Thanks for your help.
Regards,
Jeremy |
|
| Back to top |
|
|
| anvanzyl |
| Posted: Sun May 13, 2007 7:33 pm Post subject: |
|
|
Novice
Joined: 03 Jan 2005
Posts: 19
Location: Melbourne, Australia
|
Firstly, not sure which version of WBIMB you are using, but my comments are based on V5.
I see that when calling your stored procedure you are passing 6 parameters but the definition only specifies 5 (3 IN and 2 OUT) - seems there is no parameter defined for the members list you want returned.
Regarding the last parameter passed in the call statement - "OutputRoot.XML.LifeMembers.Member[]" - seems you are expecting the stored procedure to return a list of members here (hope my understanding is correct). In this case this will never work as there is no definition for that OUT parameter and the type must be a scalar type (i.e. CHAR, INT etc) - no "list" or "array" types can be used.
In our case we had to use a Websphere JDBC adapter to do this for us. Furthermore, for the "array" returned from the stored proc, we had to use a "CLOB" type (basically a large string of characters).
Hooe this helps.
_________________
Regards
Andre
IBM Certified System Administrator -- WMQ V5.3
IBM Certified Solution Designer -- WBIMB V5 |
|
| Back to top |
|
|
| sparsons |
| Posted: Sun May 13, 2007 9:19 pm Post subject: |
|
|
Newbie
Joined: 11 Mar 2002
Posts: 6
|
Jeremy is calling the stored procedure correctly: See WMB Info Center article ac17040_.
I am calling an Oracle stored procedure using the same syntax. It works always works on Windows but on Solaris has the same misbehavior: Works the first time, then ORA-24333 on subsequent tries (returning nulls).
APAR IY78664 is for the same issue but on WBIMB 5 on AIX. I am using WMB 6.
Jeremy: I do not understand: "Multiple messages sent allow the stored procedure to be called multiple times." How? Can you please clarify? Thanks. |
|
| Back to top |
|
|
| jlee |
| Posted: Wed May 16, 2007 6:51 pm Post subject: |
|
|
Newbie
Joined: 03 May 2007
Posts: 7
Location: Auckland, NZ
|
Sorry for the late response - we ended up cranking a different approach such that for our purpose the system functions correctly. Some of the guys are still trying to figure out why it happened, and I actually would still love to know why and how to fix it.
To answer your question mate, I set up two flows in an attempt to find the problem.
Both flows consisted of an Input and a Compute node. So you pass a message to the input and the Compute node does its stuff. The Compute node in the first flow calls the stored procedure twice consecutively, whereas the Compute node in the second flow calls the stored procedure once.
As expected the first flow failed on the second call.
The second flow however passed (since it was only calling it once). So from this if we sent two messages to the flow, the stored procedure get called twice and works fine.
It is almost as if by ending the processing of the entire flow, some implicit closure occurs on the cursor? maybe such that when the next message goes through the stored procedure is recalled from scratch? speculation I know, but we were seriously at a loss!
Hope this helps. |
|
| Back to top |
|
|
| sparsons |
| Posted: Fri May 25, 2007 2:30 pm Post subject: APAR under investigation... |
|
|
Newbie
Joined: 11 Mar 2002
Posts: 6
|
| Support has said this APAR is still under investigation. Meanwhile, there may be some workarounds that are useful. I opened PMR 56580 999 000. |
|
| Back to top |
|
|
| Rodeo |
| Posted: Tue Jun 05, 2007 10:39 am Post subject: |
|
|
Apprentice
Joined: 11 Apr 2007
Posts: 30
|
| jlee: In the stored procedure executing on the database, is it returning a single column for the dynamic result set or multiple columns? |
|
| Back to top |
|
|
| jeffisom |
| Posted: Mon Mar 03, 2008 11:28 am Post subject: |
|
|
Newbie
Joined: 11 Oct 2007
Posts: 7
|
I am having this problem currently on our Solaris install of WMB and it's only with a single call in a flow. And based on my testing so far, it works once and then you have to wait 60 seconds without a call going to the stored proc and then it will work again.
I also can't test it on my local broker install because I can't figure out how to get the data direct driver on windows to allow me to call stored procs.
 !!! |
|
| Back to top |
|
|
| jeffisom |
| Posted: Tue Mar 04, 2008 8:22 am Post subject: |
|
|
Newbie
Joined: 11 Oct 2007
Posts: 7
|
| I discovered last night that my problem only happens when I use dynamic result sets. Anybody else seen this behavior? |
|
| Back to top |
|
|
| jeffisom |
| Posted: Tue Mar 04, 2008 4:31 pm Post subject: |
|
|
Newbie
Joined: 11 Oct 2007
Posts: 7
|
So we figured out what was causing our problem.
Even if the stored procedure can accept nulls, you can't pass them in from MB. I had to define any input parameters as empty string if I wanted to pass them in as null. Then I could call the stored procedure as many times as often as I wanted with no dreaded ORA-24333 error.
Hope this helps! |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Mar 05, 2008 2:17 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi jeffisom well done for finding out what causes this problem for you. However, this still sounds like a defect to me (as the broker should be able to send NULLs to the SP without you having to check.) If you opened a PMR for this problem and mention this thread in it, service could take a look at it...
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 |
|
|
| letmecheck |
| Posted: Sat Sep 10, 2011 5:46 am Post subject: [Oracle]ORA-24333: zero iteration count |
|
|
Novice
Joined: 27 May 2011
Posts: 12
|
I encountered the same issue and while searching the forum I came across the same issue reported by Jeffisom.
My case is a little different, I can call the same procedure (which also returns the cursor) in loop. When it is invoked with different parameters I am getting the same '[DataDirect][ODBC Oracle driver][Oracle]ORA-24333: zero iteration count'
That is, If I send different messages with different service ids, and accumulate those mesages(say 50 messages) and start the flows, SP will throw the error. But the same is not noticed for the same kind of messages(same serviceid, 50 msgs).
Could someone explain to me what Jeffisom,said as a workaround for the similar issue.It is given below.
"Even if the stored procedure can accept nulls, you can't pass them in from MB. I had to define any input parameters as empty string if I wanted to pass them in as null. Then I could call the stored procedure as many times as often as I wanted with no dreaded ORA-24333 error. "
I noticed he is invoking the SP with all the values as shown in the post.(given below)
CALL Get_Members_For_Fulfilment('04042005',
'05052006',
'LIFE',
code,
msg,
OutputRoot.XML.LifeMembers.Member[]);
Any help would be greately appreciated.
thanks |
|
| Back to top |
|
|
| letmecheck |
| Posted: Sat Sep 10, 2011 7:11 am Post subject: |
|
|
Novice
Joined: 27 May 2011
Posts: 12
|
I felt that it is happening randomly. my sample flow was unable to reproduce the issue.
Any clue?
thanks |
|
| Back to top |
|
|
|
|
