Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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
![]() |
![]() |