Home » SQL & PL/SQL » SQL & PL/SQL » cursor exit problem
cursor exit problem [message #242703] Mon, 04 June 2007 14:10 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hello You all,

I have this issue.

At the first occurence of an age field less than 20 It should exit the loop and return a TRUE. It should loop untill it finds an occurence and if an occurence of age less than 20 is not available..it should return a FALSE.

can someone tell me what I am doing wrong in this piece of code?


FOR r_mycursor IN c_mycursor
        LOOP          
           v_age := r_mycursor.age            
             IF  v_age <= 20
             THEN 
               RETURN TRUE;
             END IF;
         END LOOP;

         RETURN FALSE;
 


Thanks in advance
Re: cursor exit problem [message #242709 is a reply to message #242703] Mon, 04 June 2007 14:38 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why do you need cursor at all?

try

DECLARE 
 i number;
BEGIN
   SELECT 1 INTO i
   FROM <your_table_name>
   WHERE <condition> AND age <= 20 AND ROWNUM = 1;
   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     RETURN FALSE;
END;


HTH.
Michael
Re: cursor exit problem [message #242720 is a reply to message #242703] Mon, 04 June 2007 17:10 Go to previous messageGo to next message
mbalves
Messages: 20
Registered: May 2007
Junior Member
Hello,

your code works well when you use inside a PL SQL code. For example, if you your function name is fage, this code works well:
.....
if fage then
dbms_output.put_line('True');
else
dbms_output.put_line('False');
end if;
....
but if you try something like:
select fage from dual;
this don´t work.
But, instead of use TRUE or FALSE you use a number (0 to false and 1 to true) then works well in both case.

I'm not sure but perhaps you can't select a boolean value in a SQL command.
Re: cursor exit problem [message #242736 is a reply to message #242703] Mon, 04 June 2007 23:16 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check out michael_bialik's answer, it describes the way to do this.
In your code you have
v_age := r_mycursor.age
IF  v_age <= 20


why not simply do

 if r_mycursor.age <= 20 
Previous Topic: invalid picture format when converting from sql7 to oracle9i
Next Topic: function to check whether no is valid
Goto Forum:
  


Current Time: Sun Dec 04 02:54:43 CST 2016

Total time taken to generate the page: 0.11733 seconds