ORACLE- CASE requires an INTO?

Question

When I try the following:

declare var_type VARCHAR2(10);

begin

  var_type := 'B';

  select case var_type 
           when 'B' then 'Beans' 
           when 'L' then 'Legumes' 
         end 
    from tableOfBeans ;

end;

I get an error saying

ORA-06550: line 4, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
Error detected at position #:62

But when I don't use var_type but instead use 'B' as the condition it works nicely. Can anyone tell me why this happens and how do I fix it so I can use var_type properly?

Answer 1

You have wrapped your SQL in a begin/end block, which makes it a PL-SQL statement. In PL-SQL, Oracle is expecting a place to put the result set from the SQL. That's the source of your ORA-06550 error. You'll want a PL-SQL variable declared to hold the result of the query via the INTO.

But the SELECT statement is still a little weird. The way you have it, there's no point in having a real table "tableOfBeans" in the FROM clause.

Did you want something like this?

declare var_type VARCHAR2(10);

begin

    select case tableOfBeans.beanType
               when 'B' then 'Beans'
               when 'L' then 'Legumes'
           end
      into var_type
      from tableOfBeans;

end;

Actually, that would generate an error too if you had more than one row in tableOfBeans. Maybe what you wanted was to retrieve items from tableOfBeans where tableOfBeans.beanType = var_type. In that case you'd still need a way to store the result set. Sorry if I'm not understanding what you're trying to do. A little more detail will help.

Answer 2

The CASE statement doesn't require an INTO.
ORA-6550 is a generic error for when your SQL doesn't compile. The error is reporting that it thinks the issue is there's no INTO clause because it thinks you're trying to perform a SELECT INTO statement, like this:

SELECT t.column
  INTO var_type
  FROM tableOfBeans

I don't have an Oracle install to test against, but I have experienced Oracle 9i/10g being weird with CASE statements, sometimes requiring you to use END CASE rather than just END. END CASE is mentioned in the Oracle documentation here.

Usually you'd use DUAL for this sort of thing, testing if the variable is set:

var_type := 'B';

SELECT CASE var_type 
         WHEN 'B' then 'Beans' 
         WHEN 'L' then 'Legumes' 
       END CASE
  FROM DUAL;

Answer 3

As answered before the errors says that you have to put the result of the select into a variable.

declare 
    var_type VARCHAR2(10);
    var_into VARCHAR2(10);
begin
    var_type := 'B';

    select case var_type 
           when 'B' then 'Beans'
           when 'L' then 'Legumes'
    end
    into var_into
    from tableOfBeans;
end;

If your table tableOfBeans contains more than one row you will run into another error ORA-01422: exact fetch returns more than one row. Use pseudo table dual instead or following pl/sql code.

declare 
    var_type VARCHAR2(10);
    var_into VARCHAR2(10);
begin
    var_type := 'B';

    var_into := case var_type 
           when 'B' then 'Beans'
           when 'L' then 'Legumes'
    end;
end;