Home » SQL & PL/SQL » SQL & PL/SQL » Cursor error (Oracle 8i)
Cursor error [message #319048] Thu, 08 May 2008 17:02 Go to next message
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 #319049 is a reply to message #319048] Thu, 08 May 2008 17:05 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Is it a problem with the cursor definition or opening & osing cursor.
Re: Cursor error [message #319050 is a reply to message #319048] Thu, 08 May 2008 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Is it a problem with the cursor definition or opening & osing cursor.
YES!
Re: Cursor error [message #319051 is a reply to message #319048] Thu, 08 May 2008 17:22 Go to previous messageGo to next message
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
IF v_sr_cur%isopen THEN

which may evaluate to false.
Then what should I try?
Re: Cursor error [message #319053 is a reply to message #319048] Thu, 08 May 2008 17:57 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
I/we have NO idea what this is or does ->
v_sr_cur DEPT_PKG.SR_CUR;

>opening or closing of the cursor
Exactly where do you OPEN the cursor?

http://asktom.oracle.com has many, many fine working code examples.

First make it work, then make it fancy.
Re: Cursor error [message #319054 is a reply to message #319048] Thu, 08 May 2008 18:04 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Well
v_sr_cur DEPT_PKG.SR_CUR; 

is defined as a REF Cursor in DEPT_PKG.
Here is the code

TYPE SR_CUR IS REF CURSOR;

[Updated on: Thu, 08 May 2008 18:06]

Report message to a moderator

Re: Cursor error [message #319069 is a reply to message #319054] Thu, 08 May 2008 22:36 Go to previous messageGo to next message
psingh7777
Messages: 22
Registered: August 2007
Location: New Delhi
Junior Member

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 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: Converting Minutes to Quarter Hours
Next Topic: Merge problem
Goto Forum:
  


Current Time: Mon Dec 05 10:42:28 CST 2016

Total time taken to generate the page: 0.10738 seconds