ORA-01002:fetch out of sequence problem in reports 2.5


        When I run a report , it gives an error as
           ORA-01002:fetch out of sequence problem.
The report contains  only couple of queries with parameters.  When I run the same report in different env, it works fine. But in production env, it displays with the above  error message. The report works fine If i run for individual item but if I give % to run for all items. Then It displays an error message.
           I asked my DBA to check the Rollaback segment, he says it is ok. I am still not able to understand, what might be the problem. Can somebody help me on this problem.  I am using Oralce report 2.5. One query has some four joins.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

mathavraConnect With a Mentor Commented:
I remember this error. We used to get this in R 2.1. I am not sure whether it will work for you. But it is worth a try.

Try the following workarounds:

1. Edit the report in Reports Designer:

     - Open Reports Designer
     - Find and open the relevant .rdf file that is causing the problem.
     - Go to Menu -> Tools -> Tools Option -> Runtime Settings and set
       Non-blocking SQL to unchecked
     - The go to Menu -> File -> Compile All.
     - Save the .rdf file

Note: Make a copy of the .rdf file before you edit the one that was installed with Designer as a safeguard measure.  Edit the supplied file and not the copied one, otherwise the changes will not been seen in the Repository Reports area.

  2. In Windows 95 and NT look at the following registry key:

     Change this to:

        EXECUTE_REPORTS_RUNTIME = C:\ORAWIN95\BIN\R25RUN32.EXE nonblocksql=no

Note: This means all reports may be passed this at runtime.

Let me know whether this fixed your problem.
If it doesn't solve, would you please put those 2 queries in here..???

   ORA-01002: Fetch Out of Sequence

The information applies to both PL/SQL and the Oracle precompilers.

Putting it in very simple terms, the error 'ORA-01002 Fetch Out of            
Sequence' means that the user tried to fetch from a cursor when            
the cursor is no longer valid.      

This article provides several scenarios in which you would receive
the error.

Explanation 1
One of the most common cases of the error is when you execute a fetch
beyond a fetch which returns a 'no data found' error, i.e., ORA-01403.

Solution 1
A fetch always returns the current row(s), and hence after retrieving
the last row(s), you must execute another fetch to determine if you are
done with retrieving data.  At this time, the fetch must be terminated,
otherwise, you receive an ORA-01002.

Example 1

The following PL/SQL block demonstrates the error.  In this example, the
fetch is in an infinite loop and there is no logic to terminate the fetch
after the last row is retrieved, and so the error is returned.                                                    
       EMP_NAME EMP.ENAME%TYPE;                                                
       CURSOR C1 IS SELECT ENAME FROM EMP;                                    
       OPEN C1;                                                                
          FETCH C1 INTO EMP_NAME;                                              
       END LOOP;                                                              
       CLOSE C1;                                                              
Explanation 2

You can also receive this error when you perform a fetch across commits
in a select for update.  

Example 2

An example PL/SQL block is as follows:
       EMP_NAME EMP.ENAME%TYPE;                                                
       OPEN C1;                                                                
          FETCH C1 INTO EMP_NAME;                                              
          UPDATE EMP SET SAL = 50;                                            
              -- cannot do this commit in version 6                            
       END LOOP;                                                              
       CLOSE C1;                                                              
In the above PL/SQL block, there is a commit across fetches in a            
select for update which causes the ORACLE kernel to return a 'Fetch
Out of Sequence'.                                                      
To examine the second case in detail, let us first discuss the aspect
of locking.  Locks are used to assure database consistency and integrity.  
A lock on a resource is acquired by a user when the user wants to prevent
another user from doing something that also requires the resource.  The
lock is released when commits or rollbacks occur and the first no longer
requires the resource.              
To maintain database consistency, you must ensure that no one else
deletes/updates the same rows selected for updating/deleting by you until
the transaction is commited or rolled back.  Hence, row locks for a select
for update are obtained in advance when you open the cursor.  

A commit or rollback then terminates this query and all locks are released
so that a commit across fetches for a select for update is not possible.  
At this point, the cursor is no longer valid and a fetch returns the
'fetch out of sequence'.


The solution to this problem is to move the commit outside the fetch loop or
use a simple select rather than a select for update.                              

The examples discussed in this article are PL/SQL examples.  The information
also applies to a program written in any high level language supported by
Oracle precompilers.
patrick10Author Commented:
Thanks it worked, Now I can see the report. Thanks for your explanation. Can you tell me regarding Nonblock ing Option ?.
Here is the description for Non-blocking operation. There is always confusuion arises with Asynchronus and Non-blocking. So, I included the Asynchronus definition as mentioned in a Oracle document.

* NONBLOCKING Operation - means that an operation can be started (such as a READ for query data by an Oracle client application), and the operation will return a special code which says I would have blocked here to wait for this to
finish, but I'm returning this special code instead. In this case, the operation has started to execute, but control is returned to the caller, so other work can be done. The caller will poll occasionally to see if the operation has completed. If so, the completion code will be returned (with the data for instance). Otherwise the special I would have blocked here code is returned, and the application goes away for a little more time before
polling again.
* ASYNCHRONOUS Operation - means that the unlike the non-blocking  case, the operation is started immediately, and the application  registers a Callback function which will be called when the operation completes. Note that no polling is required. The asynchronous  operation will complete in it's own good time, and the calling  application doesn't need to be concerned with it again until the  callback function is executed, (or maybe until a timer expires that the application has set to limit the total length of time the  operation could possible take). The application is free to do anything it wants while the operation is executing. There is no asynchronous  functionality being delivered for Oracle client-side interfaces at this time.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.