Q
Problem solve Get help with specific problems with your technologies, process and projects.

CASE in definition of cursor

Why isn't CASE supported by PL/SQL in definition of cursor? It doesn't work.

Why isn't CASE supported by PL/SQL in definition of cursor? I used the following command:
declare
temp varchar2(100);
cursor cDet is
select
case    when length('pippo - pluto - paperino')>5   then 'OK' else 'NO'
END
from dual;
begin
open cDet;
fetch cDet  into temp;
    WHILE cDet%found LOOP
        dbms_output.put_line(temp);
        fetch cDet into temp;
    end loop;
    close cDet;
end;
It doesn't work, but if I use the following one it works fine:
select
case    when length('pippo - pluto - paperino')>5   then 'OK' else 'NO'
END
from dual
Why?
The case statement is supported by PL/SQL. The problem may be with your PL/SQL procedure. The below procedure works:
declare
   temp varchar2(100);
   cursor cdet is
     select 
    case 
       when length('pippo - pluto - paperino' )>5 then
          'OK'
        else
            'No'
    end
from dual;
begin
    open cdet;
      fetch cdet into temp;
       -- while cdet%found loop
         dbms_output.put_line('temp '||temp);
         --   fetch cdet into temp;
       -- end loop;
        
      close cdet;
end;

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close