Using BO 4.1.2, Oracle 7.3 w/ SQLnet 2.3, I occasionally experience an error while the query is fetching which says ‘ORA-01002 Fetch out of Sequence’. While this is an Oracle error message, it only happens when running the query via BO (i.e. SQL Plus is OK, DBArtisan OK, etc).
I have gathered information from both BO and Oracle, but both claim the problem to be of the other’s doing, and have been completely unhelpful. I am inclined to say it is a BO-Oracle driver problem. The only things that seem to help are if I somehow alter the size of the result set (unacceptable) or change the BO parameter settings to run in ‘Synchronous Mode’ (also unacceptable, since then we can’t cancel queries).
Does anyone have any info or experience w/ this problem?? Thanks!
Does anyone have any info or experience w/ this problem?? Thanks!
This error happens really infrequently and is very hard to reproduce. You normally can get around this error by re-creating the query from scratch. So if you are not able to change an existing query, you will probably be able to start a new query and get it to work. Craig Stark
g GE Electrical Distribution & Control
Using BO 4.1.2, Oracle 7.3 w/ SQLnet 2.3, I occasionally experience an
error while the query is fetching which says ‘ORA-01002 Fetch out of Sequence’. While this is an Oracle error message, it only happens when running the query via BO (i.e. SQL Plus is OK, DBArtisan OK, etc).
I have gathered information from both BO and Oracle, but both claim the problem to be of the other’s doing, and have been completely unhelpful. I am inclined to say it is a BO-Oracle driver problem. The only things that seem to help are if I somehow alter the size of the result set (unacceptable) or change the BO parameter settings to run in ‘Synchronous Mode’ (also unacceptable, since then we can’t cancel queries).
Does anyone have any info or experience w/ this problem?? Thanks!
We found that this error was caused in our system because the Universe Parameters for the connection properties were set to Disconnect after each transaction. Apparently what was happening was that BO sent requests to fetch the data in a cursor and then closed the connection after each cursor was done. After the last fetch, when the connection restarted, BO attempted to open another cursor and perform a fetch. [BTW, we discovered this through using BOLogApp.exe to examine the processes between BO and Oracle]. Our solution was to set the connection properties to Keep the connection active for __ minutes.
I faced this problem at a client’s site. The problem occurred when reports were run
on a particular NT server (which held the repository and DAS). When run on another
NT server or on a Windows 95 client, the problem did not appear.
Further, a given query would give this error on one day, then run successfully the next day
after the database had been refreshed. The number of rows returned would vary slightly
from day to day but not drastically.
Business Objects’ recommendation was to reduce the fetch array size to 1. That parameter
is found on the ‘Advanced’ tab of the connection definition. This change reduced the
fequency of the problem but did not cure it.
The NT server which had the problem died and we replaced it with another. The problem has never recurred.
Larry Rosenblum
Saama Technologies, Inc.
Jason Beard wrote:
Hello all,
Using BO 4.1.2, Oracle 7.3 w/ SQLnet 2.3, I occasionally experience an error while the query is fetching which says ‘ORA-01002 Fetch out of Sequence’. While this is an Oracle error message, it only happens when running the query via BO (i.e. SQL Plus is OK, DBArtisan OK, etc).
I have gathered information from both BO and Oracle, but both claim the problem to be of the other’s doing, and have been completely unhelpful…[snip]
Does anyone have any info or experience w/ this problem?? Thanks!
<< I encountered this problem once with BO 4.1.4, Oracle 7.3 and SQL*Net 2.3. I worked around it by changing my connection settings to “disconnect after each transaction”. The value for “Array fetch size” was set to 500. After this change, the error never appeared again.
Brent
‘Fetch out of Sequence’
What is this error in BO? When I run the same SQL with Oracle client it runs
fine and gives me results.
I changed the fetch array size from 20 to 50 and that worked. Thanks, Brent.
But a small question here, what is the impact on system memory with the increase of array fetch size?
I cut this from our archives - hope it helps:
<< I encountered this problem once with BO 4.1.4, Oracle 7.3 and SQL*Net 2.3. I worked around it by changing my connection settings to “disconnect after each transaction”. The value for “Array fetch size” was set to 500. After this change, the error never appeared again.
Brent
‘Fetch out of Sequence’
What is this error in BO? When I run the same SQL with Oracle client it runs
fine and gives me results.
Hi Fellow listers,
I thought I knew to handle this error, but it is kind of hazy.
I think we need to increase the ARRAY FETCH SIZE (or is it decrease?) for the connection the Universe is using. However apart from the increase OR decease question, my another question is, Do I have to export this universe after we edit the connection in Designer. I see that the connection type is secured. So, If I edited connection, do I edit the actual connection…No need to export Universe???
Vinay,
You dont need to export the universe. You have actually edited the connection. Just make sure
that you logout of the reporter and login again to see the effect.
Durgesh
Hi Fellow listers,
I thought I knew to handle this error, but it is kind of hazy.
I think we need to increase the ARRAY FETCH SIZE (or is it decrease?) for the connection the Universe is using. However apart from the increase OR decease question, my another question is, Do I have to export this universe after we edit the connection in Designer. I see that the connection type is secured. So, If I edited connection, do I edit the actual connection…No need to export Universe???
Hi,
I’ve got a report with multiple data providers ;when i try to refresh that report - 5 of them are running fine but in one of them its saying "Connection or SQL sentence error : fetch out of sequence " .Though the SQL of that particular runs fine in SQL Plus and BO Freehand SQL .Looks like there is nothing wrong with the query then why is this problem occurring in BO only …Any ideas …???
TIA,
Vikas Vats
_________________________________________________ g GE, Lighting, Cleveland
Tel # 216-266-8923(Off)
Dial Comm 8-346-8923
440 442 0169(Res)
BO says that is a “network issue”. You can either try again later or reduce your array fetch size in your connection (you might have to go as low as 1 to get it to run). This doesn’t always work, (at least not for us) however and it will make your reports take much longer to run. I usually cut it in half (I have it set to 100 right now) and if it doesn’t work I just try again in a few hours and it’s usually ok.
Mike Ciance
CIT
Hi,
I’ve got a report with multiple data providers ;when i try to refresh that report - 5 of them are running fine but in one of them its
saying "Connection or SQL sentence error : fetch out of sequence " .Though
the SQL of that particular runs fine in SQL Plus and BO Freehand SQL .Looks
like there is nothing wrong with the query then why is this problem occurring in BO only …Any ideas …???
TIA,
Vikas Vats
_________________________________________________ g GE, Lighting, Cleveland
Tel # 216-266-8923(Off)
Dial Comm 8-346-8923
440 442 0169(Res)
Have you tried setting the array fetch size to 1? I believe this is the solution that has been discussed here in the past. Might check the archives to make certain.