Solved

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

Posted on 2001-06-07
5
1,051 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now