| ORA-01002: fetch out of sequence [message #129976] |
Wed, 27 July 2005 09:08  |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
Hello --
I am not sure if this is some sort of "oracle bug" or an internal program bug. I am getting ORA-01002: fetch out of sequence while trying to commit/rollback inside a cursor. In a nutshell my program is doing the following:
1. Declare a cursor inside package spec:
CURSOR c_test(c_id NUMBER)
IS
SELECT id
FROM test_table
WHERE id = c_id;
2. Inside package body I open my cursor as follows:
OPEN cv FOR lv_stmt; -- this is dynamicaly build string
LOOP
FETCH cv INTO some variables ..
-- I do lots of logic here and call procedure A
-- Inside this procedure I preform and insert.
-- I get out of this procedure back to the main and
-- depending on some additional conditions I either ROLLBACK or
-- COMMIT. I perform COMMIT/ROLLBACK inside loop so that it happens per record. Why am I getting fetch out of sequence if the cursor is still open?
thanx!
|
|
|
|
| Re: ORA-01002: fetch out of sequence [message #129991 is a reply to message #129976] |
Wed, 27 July 2005 10:49  |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
Check carefully aren't you using any cursor with FOR UPDATE
clause. COMMIT in conjunction with this clause causes this
problem:
SQL> declare
2 cursor s is select ename from emp for update;
3 ename emp.ename%type;
4 begin
5 open s;
6 loop
7 fetch s into ename;
8 exit when s%notfound;
9 ---something
10 commit;
11 end loop;
12 close s;
13 end;
14 /
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 7
Rgds.
|
|
|
|
| Re: ORA-01002: fetch out of sequence [message #129995 is a reply to message #129976] |
Wed, 27 July 2005 10:59 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
|
nop. not using FOR UPDATE statement. ALthough I understand that commit would essentially close the transaction..so the cursor would be closed. What is the solution then? I absolutelly have to commit per record (or rollback) in several places...
|
|
|
|
| Re: ORA-01002: fetch out of sequence [message #129998 is a reply to message #129995] |
Wed, 27 July 2005 11:11 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
As you can see commit doesn't affect usual cursor:
SQL> declare
2 cursor s is select ename from emp;
3 ename emp.ename%type;
4 begin
5 open s;
6 loop
7 fetch s into ename;
8 exit when s%notfound;
9 ---something
10 commit;
11 end loop;
12 close s;
13 end;
14 /
PL/SQL procedure successfully completed.
Only FOR UPDATE cursor will be affected by commit.
I'm confused with you statement:
| Quote: |
1. Declare a cursor inside package spec:
CURSOR c_test(c_id NUMBER)
IS
SELECT id
FROM test_table
WHERE id = c_id;
2. Inside package body I open my cursor as follows:
OPEN cv FOR lv_stmt; -- this is dynamicaly build string
LOOP
FETCH cv INTO some variables ..
|
You are talking of c_test cursor but use CV cursor variable
which is opened dynamically.
Are you absolutely sure you dynamic cursor is not opened
in FOR UPDATE mode ? See below:
SQL> declare
2 s sys_refcursor;
3 ename emp.ename%type;
4 begin
5 open s for 'select ename from emp for update';
6 loop
7 fetch s into ename;
8 exit when s%notfound;
9 ---something
10 commit;
11 end loop;
12 close s;
13 end;
14 /
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 7
Rgds.
|
|
|
|
| Re: ORA-01002: fetch out of sequence [message #130003 is a reply to message #129998] |
Wed, 27 July 2005 11:33 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
no..no FOR UPDATE..
PROCEDURE p_main_process (v_base_bcat_p VARHCAR2,
v_inc_ecls2_p VARCHAR2,
v_inc_ecls_P VARCHAR2) IS
-- here is the ref cursor
TYPE cv_typ IS REF CURSOR;
cv cv_typ;
-- Structures for non-primary jobs
TYPE non_prim_job_record IS RECORD
(g_pidm nbrjobs.nbrjobs_pidm%TYPE,
g_posn nbrjobs.nbrjobs_posn%TYPE,
g_suff nbrjobs.nbrjobs_suff%TYPE);
tbltype non_prim_job_record;
v_jobs_cur t_RefCur;
-- will fetch into these vars
emp_id NUMBER;
emp_last_name VARCHAR2(50);
emp_code_home VARCHAR2(50);
lv_sttm (4000) := 'SELECT id, name, code
FROM tableA, tableB
WHERE id > 10000 || NVL(v_base_bcat_p,' AND 1=1') || NVL(v_inc_ecls2_p, ' AND 1=1')
|| NVL(v_inc_ecls_p, ' AND 1=1');
BEGIN
OPEN cv FOR lv_stmt;
LOOP
FETCH cv INTO emp_id, emp_last_name, emp_code_home;
EXIT WHEN cv%NOTFOUND;
--call a function that returns ref cursor like this:
v_jobs_cur :=
F_get_non_primary_jobs(emp_id)
LOOP
FETCH v_jobs_cur INTO tbltype;
EXIT WHEN v_jobs_cur%NOTFOUND;
-- Call Procedure that perfomrs several inserts and updates
-- there is no rollback or commit in that procedure
IF(some condition) THEN
COMMIT;
END IF;
END LOOP;
CLOSE v_jobs_cur; --inner cursor
.. more logic, more inserts (no commits or rollbacks)
IF (some other condition) THEN
COMMIT;
END IF;
END LOOP;
CLOSE cv;
|
|
|
|
| Re: ORA-01002: fetch out of sequence [message #130076 is a reply to message #130003] |
Thu, 28 July 2005 01:45 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
| Quote: |
--call a function that returns ref cursor like this:
v_jobs_cur :=
F_get_non_primary_jobs(emp_id)
LOOP
FETCH v_jobs_cur INTO tbltype;
EXIT WHEN v_jobs_cur%NOTFOUND;
-- Call Procedure that perfomrs several inserts and updates
-- there is no rollback or commit in that procedure
IF(some condition) THEN
COMMIT;
END IF;
|
It looks like F_get_non_primary_jobs returns for update cursor.
Can you check it ?
My advise can be nonsense because I know nothing of
your logic and cursor details from this function, but
1) Try to change logic to avoid commit/rollback inside
loop. In very most cases the often commit is a nonsense. Just change logic so that the insertion/updation would be performed depending on commit conditions (if you can do it).
1) you can try to wrap your logic between EXIT WHEN... and
END LOOP; by autonomous transaction procedure. I can't guaranty
the success because it can lead to ORA-00060 deadlock problem.
Rgds.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
