Cursor error [message #319048] |
Thu, 08 May 2008 17:02  |
apps_user
Messages: 35 Registered: May 2008
|
Member |
|
|
I have the following error from this code:
ORA-01001: invalid cursor
DECLARE
v_sr_cur DEPT_PKG.SR_CUR;
v_dept VARCHAR2(40);
v_dept_no NUMBER;
v_emp VARCHAR2(40);
v_emp_no NUMBER;
v_first_name VARCHAR2(40);
v_last_name VARCHAR2(40);
v_emp_pos VARCHAR2(40);
BEGIN
EMP_PKG.GET_DEPT_DATA(v_emp_id,v_dept_id,v_sr_cur);
LOOP
dbms_output.put_line('in the loop');
FETCH v_sr_cur INTO v_dept
,v_dept_no
,v_emp
,v_emp_no
,v_first_name
,v_last_name
,v_emp_pos;
EXIT WHEN v_sr_cur%NOTFOUND;
END LOOP;
CLOSE v_sr_cur;
Any idea why it is happening like this?
|
|
|
|
|
Re: Cursor error [message #319051 is a reply to message #319048] |
Thu, 08 May 2008 17:22   |
apps_user
Messages: 35 Registered: May 2008
|
Member |
|
|
Sorry,I should put it this way.
Is it a problem with
1) cursor declaration in the declare section of the program
DECLARE
v_sr_cur DEPT_PKG.SR_CUR;
v_dept VARCHAR2(40);
v_dept_no NUMBER;
v_emp VARCHAR2(40);
v_emp_no NUMBER;
v_first_name VARCHAR2(40);
v_last_name VARCHAR2(40);
v_emp_pos VARCHAR2(40);
2) opening or closing of the cursor
LOOP
dbms_output.put_line('in the loop');
FETCH v_sr_cur INTO v_dept
,v_dept_no
,v_emp
,v_emp_no
,v_first_name
,v_last_name
,v_emp_pos;
EXIT WHEN v_sr_cur%NOTFOUND;
END LOOP;
CLOSE v_sr_cur;
If it is #2 then what may be wrong?
I tried the following way,still i have issues.
DECLARE
v_sr_cur DEPT_PKG.SR_CUR;
v_dept VARCHAR2(40);
v_dept_no NUMBER;
v_emp VARCHAR2(40);
v_emp_no NUMBER;
v_first_name VARCHAR2(40);
v_last_name VARCHAR2(40);
v_emp_pos VARCHAR2(40);
BEGIN
EMP_PKG.GET_DEPT_DATA(v_emp_id,v_dept_id,v_sr_cur);
IF v_sr_cur%isopen THEN
LOOP
dbms_output.put_line('in the loop');
FETCH v_sr_cur INTO v_dept
,v_dept_no
,v_emp
,v_emp_no
,v_first_name
,v_last_name
,v_emp_pos;
EXIT WHEN v_sr_cur%NOTFOUND;
END LOOP;
CLOSE v_sr_cur;
END IF;
END;
It doesnot go to loop section at all.
It might be
which may evaluate to false.
Then what should I try?
|
|
|
|
|
Re: Cursor error [message #319069 is a reply to message #319054] |
Thu, 08 May 2008 22:36   |
|
Nothing is clear from your text,
Please specify what error you are getting,
is the cursor out variable in package procedure
try using
open v_sr_cur
FETCH v_sr_cur INTO v_dept
,v_dept_no
,v_emp
,v_emp_no
,v_first_name
,v_last_name
,v_emp_pos;
close v_sr_cur;
|
|
|
Re: Cursor error [message #319075 is a reply to message #319069] |
Thu, 08 May 2008 23:51  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
v_sr_cur should be opened by EMP_PKG.GET_DEPT_DATA.
Please show us that you do open it there, you don't fetch it there, you don't close it there and that you return a ref there.
|
|
|