Re: ORA 1002. Fetch Out of sequence error

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 Wed Feb 24 1999 - 12:50:56 CST