RefCursor not returning all data

[rshivagami]

I have a storedproc that returns a RefCursor. When I copy paste the SELECT query from the storedproc in SQLPLUS and run it separately, I get the right data from it. But when I execute the storedproc and read the data in the refcursor via a PRINT, only partial data shows up.
All that the storeproc has is one complex SELECT statement. Any ideas on how I go about debugging this ? I am running Oracle 9.2.0.6 on Linux.

Thanks for any input.

[rshivagami]

Another weird datapoint. Here is the partial spool file when I first ran the storeproc.

SQL> exec reporting.quiz_roster_report(8004, null, 74113, :c);

PL/SQL procedure successfully completed.

SQL> print c;

SUBJECT_DESC   LESSON_DESC     STUDENT_NAME Lesson_Average Quiz1_Average        
-------------- --------------- ------------ -------------- -------------        
CRITICAL READI Passage-based R       398100                   39.2857143        
CRITICAL READI Passage-based R       665000                   39.2857143        
CRITICAL READI Passage-based R       748000                           25        
CRITICAL READI Passage-based R       286713                                     
CRITICAL READI Passage-based R       656613     42.8571429    53.5714286        
CRITICAL READI Passage-based R       656976                   53.5714286        
CRITICAL READI Passage-based R       657021                   64.2857143        
CRITICAL READI Passage-based R       658039                   64.2857143        
CRITICAL READI Passage-based R       659029                   53.5714286        
CRITICAL READI Passage-based R       660775                   35.7142857        
CRITICAL READI Passage-based R       660607                   57.1428571

Here is the output I got when running the exact same proc after 5 minutes:

SQL> exec reporting.quiz_roster_report(8004, null, 74113, :c);

PL/SQL procedure successfully completed.

SQL> print c

SUBJECT_DESC   LESSON_DESC     STUDENT_NAME Lesson_Average Quiz1_Average        
-------------- --------------- ------------ -------------- -------------        
CRITICAL READI Passage-based R       398100                   39.2857143        
CRITICAL READI Passage-based R       665000                                     
CRITICAL READI Passage-based R       748000                                     
CRITICAL READI Passage-based R       286713                                     
CRITICAL READI Passage-based R       656613     42.8571429    53.5714286        
CRITICAL READI Passage-based R       656976                                     
CRITICAL READI Passage-based R       657021                                     
CRITICAL READI Passage-based R       658039                                     
CRITICAL READI Passage-based R       659029                                     
CRITICAL READI Passage-based R       660775                                     
CRITICAL READI Passage-based R       660607

As you can see, there is data missing between the 2 calls. Nothing has been changed in the storedproc in these 5 minutes and nothing has changed datawise also.

Thanks.

[rshivagami]

Update :
I opened a TAR with oracle and this workaround they suggested surprisingly works :

create a view or temptable that holds the data from the SQL and then Select from this view or temptable into the RefCursor.

Still not sure of the root cause.