Solved

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

Posted on 2001-06-07
5
1,088 Views
Last Modified: 2008-02-26
Hi.

        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.


 
0
Comment
Question by:patrick10
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 3

Accepted Solution

by:
mathavra earned 50 total points
ID: 6165207
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:
 
        EXECUTE_REPORTS_RUNTIME = C:\ORAWIN95\BIN\R25RUN32.EXE

     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.
0
 
LVL 2

Expert Comment

by:jammalk
ID: 6165214
If it doesn't solve, would you please put those 2 queries in here..???
0
 

Expert Comment

by:alurirk
ID: 6166518

   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.                                                    
                                                                               
 
    DECLARE                                                                    
 
       EMP_NAME EMP.ENAME%TYPE;                                                
 
       CURSOR C1 IS SELECT ENAME FROM EMP;                                    
 
    BEGIN                                                                      
 
       OPEN C1;                                                                
 
       LOOP                                                                    
 
          FETCH C1 INTO EMP_NAME;                                              
 
       END LOOP;                                                              
 
       CLOSE C1;                                                              
 
    END;                                                                      
 
                                                                               
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:
 
    DECLARE                                                                    
 
       EMP_NAME EMP.ENAME%TYPE;                                                
 
       CURSOR C1 IS SELECT ENAME FROM EMP FOR UPDATE OF SAL;                  
 
    BEGIN                                                                      
 
       OPEN C1;                                                                
 
       LOOP                                                                    
 
          FETCH C1 INTO EMP_NAME;                                              
 
          UPDATE EMP SET SAL = 50;                                            
 
              -- cannot do this commit in version 6                            
 
          COMMIT;                                                              
 
       END LOOP;                                                              
 
       CLOSE C1;                                                              
 
    END;                                                                      
 
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'.


Solution
--------

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.                              
                                                                               
 
Note
----

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.
0
 

Author Comment

by:patrick10
ID: 6167820
Thanks it worked, Now I can see the report. Thanks for your explanation. Can you tell me regarding Nonblock ing Option ?.
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6168332
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.
 
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question