Re: ORA 1002. Fetch Out of sequence error

From: Anthony Nicholls <_at_cablinet>
Date: Thu, 25 Feb 1999 09:15:19 +0000
Message-ID: <7b34gk$7b0$1@news1.cableinet.co.uk>

I have had this error on an Access 2.0 database that was using tables linked to an Oracle 7.1 database table. Here is what happened in my case:

A query was executed in Access, it took about 1 minute to run and displayed all of the results quite happily.
I tried to print the results from the datasheet. It took about 10 minutes to print, and the printout printed the first few records okay, and then the rest of the records had #Name in all of the fields.
I re-ran the query again straight after printing, and the query took only about 1 minute to run. This time however, as I scrolled through the records, it took about 5 minutes to go through each record.

This problem still remains.

I then had another problem where a query runs fine until criteria that seems okay is specified. The ORA-01002 error is reported, and the records return #Name. This problem still remains.

It may be co-incidence that the two problems are related, but with Access anything is possible.

Remco Blaakmeer wrote:

> In article <7b16fp$p15$1_at_nnrp1.dejanews.com>,
> m_popov_at_hotmail.com writes:
> > All the variables are properly declared. I am getting Fetch out of sequence
> > error ORA-01002 where I marked with ----> in the code below. The cursor
> > declaration is in the beginning o fthe program.
> >
> >
> > cursor c2_get_tests is
> > select substr(description_text,1,15), result
> > from descriptions_at_dmscprod a, sap_test_results_at_dmscprod b
> > where language_code = 'E'
> > and table_name = 'TESTS'
> > and b.control_number = lc_ctrl_num
> > and b.test_code||b.center_code = a.ukey;
> > c2_get_tests_rec c2_get_tests%ROWTYPE;
> > ....
> > ....
> > ....
> > open c2_get_tests;
> > loop
> > -----> fetch c2_get_tests into lc_test_name, lc_results;
> > exit when c2_get_tests%NOTFOUND;
> > rec1 :='BXXSEAN'|| lc_ctrl_num ||lc_test_name;
> > insert_to_tmp;
> > end loop; /* c2_get_tests */
> > close c2_get_tests;

> Not that I think this will solve your problem (it might), but I would
> have written that loop with a FOR statement, something like this:

>


>                 cursor c2_get_tests is


>                 select substr(description_text,1,15) test_name


>                 , result


>                 from descriptions_at_dmscprod a, sap_test_results_at_dmscprod b


>                 where language_code = 'E'


>                 and table_name = 'TESTS'


>                 and b.control_number = lc_ctrl_num


>                 and b.test_code||b.center_code = a.ukey;


>                 c2_get_tests_rec c2_get_tests%ROWTYPE;


>                  ....


>                  ....


>                  ....


>                 for c2_get_tests_rec in c2_get_tests loop


>                     lc_test_name := c2_get_tests_rec.test_name;


>                     lc_results := c2_get_tests_rec.result;


>                     rec1 :='BXXSEAN'|| lc_ctrl_num ||lc_test_name;


>                     insert_to_tmp;


>                 end loop; /* c2_get_tests */


>

> This way, you don't need to open, fetch and close the cursor and you
> don't need to declare the c2_get_tests_rec variable. Plus it makes your
> code a lot easier to read.

> In case you don't have the docs at hand, here's what the on-line
> documentation says about this error:

> ORA-01002: fetch out of sequence

> Cause: In a host language program, a FETCH call was issued out of sequence.
> A successful parse-and-execute call must be issued before a fetch. This can
> occur if an attempt was made to FETCH from an active set after all records
> have been fetched. This may be caused by fetching from a SELECT FOR UPDATE
> cursor after a commit. A PL/SQL cursor loop implicitly does fetches and may
> also cause this error.

> Action: Parse and execute a SQL statement before attempting to fetch the
> data.

> Remco
> --
> rd31-144: 7:40pm up 18:29, 5 users, load average: 1.11, 1.09, 1.03
Received on Thu Feb 25 1999 - 03:15:19 CST