Home » SQL & PL/SQL » SQL & PL/SQL » Cursor Doubt
Cursor Doubt [message #209471] Fri, 15 December 2006 01:08 Go to next message
vp17in
Messages: 12
Registered: November 2006
Junior Member
IF e_ver = 1 THEN
OPEN emp_cur FOR
SELECT e_name
, e_id
, e_startDate
FROM emp
WHERE e_no = i_no;
ELSE
OPEN emp_cur FOR
SELECT NULL,NULL,NULL
FROM DUAL;

If e_ver is not 1 then should I write an explicit else(Like in the above code) to open the cursor and populate with null, or will oracle handle on its own(like populating the cursor with null on its own).

My doubt is if I don't write the else and if the ver is NOT one will this end up in exception or the emp_cur is populated with null by oracle.
Re: Cursor Doubt [message #209476 is a reply to message #209471] Fri, 15 December 2006 01:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd put the ELSE in.
Otherwise you won't have an open cursor, and you'll get an Invalid Cursor exception raised when you try to Fetch from it.

Here's what happens without the ELSE
SQL> declare
  2    c_ref   sys_refcursor;
  3    
  4    v_string   varchar2(10);
  5    v_ver      pls_integer := 2;
  6  begin
  7    if v_ver = 1 then
  8      open c_ref for select '1' from dual;
  9    end if;
 10    
 11    fetch c_ref into v_string;
 12    close c_ref;
 13  end;
 14  /
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 11
and here's what happens with the ELSE
SQL> declare
  2    c_ref   sys_refcursor;
  3    
  4    v_string   varchar2(10);
  5    v_ver      pls_integer := 2;
  6  begin
  7    if v_ver = 1 then
  8      open c_ref for select '1' from dual;
  9    else
 10      open c_ref for select null from dual;
 11    end if;
 12    
 13    fetch c_ref into v_string;
 14    close c_ref;
 15  end;
 16  /

PL/SQL procedure successfully completed.


Would it have been so hard for you to check this yourself?

[Edited to add examples]

[Updated on: Fri, 15 December 2006 01:53]

Report message to a moderator

Re: Cursor Doubt [message #209510 is a reply to message #209476] Fri, 15 December 2006 04:39 Go to previous messageGo to next message
vp17in
Messages: 12
Registered: November 2006
Junior Member
sorry about my previous question. That was not correct. Here is my question

This is my cursor

declare
emp_cur sys_refcursor;
OPEN emp_cur FOR
SELECT e_name
, e_id
, e_startDate
FROM emp
WHERE e_no = i_no;

If the table (emp) doesn't has any data what will happen? Will it result in a Invalid Cursor exception.
Re: Cursor Doubt [message #209516 is a reply to message #209510] Fri, 15 December 2006 05:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Writing this example took me about 2 minutes 30 seconds.
You will learn more by getting into the habit of working things out for yourself and asking question when stuck than you will by asking questions as a first resort.
SQL> create table temp_cursor (col_1  varchar2(10));

Table created.

SQL> declare
  2    c_ref   sys_refcursor;
  3    
  4    v_string   varchar2(10);
  5  begin
  6    open c_ref for select col_1 from temp_cursor;
  7    
  8    fetch c_ref into v_string;
  9    close c_ref;
 10  end;
 11  /

PL/SQL procedure successfully completed.

What you will want to read up on Cursor Attributes, particularly %NOTFOUND, which tells you when you've reached the end of a cursors record set.
Re: Cursor Doubt [message #209794 is a reply to message #209516] Sun, 17 December 2006 20:55 Go to previous message
vp17in
Messages: 12
Registered: November 2006
Junior Member
Thank you very much. I will follow your advice.
Previous Topic: Optimization
Next Topic: Spool file query
Goto Forum:
  


Current Time: Sun Dec 11 08:29:27 CST 2016

Total time taken to generate the page: 0.05928 seconds