DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle's CASE statement

  1. #1
    Join Date
    Nov 2001
    Posts
    15
    Following CASE statement works from sqlplus:
    --------------------------------------------------------
    select case when 1=1 then '1'
    else '2'
    end as "xyz"
    FROM dual;
    --------------------------------------------------------
    However, following does not even compile:
    SQL> CREATE OR REPLACE PROCEDURE INT_SUBBASKETUPDSP
    2 AS
    3 v_1 varchar2(20);
    4 BEGIN
    5 select case when 1=1 then '1'
    6 else '2'
    7 end as "xyz"
    8 INTO v_1
    9 FROM dual;
    10 dbms_output.put_line('The value is : '||v_1);
    11 END;
    12 /

    Warning: Procedure created with compilation errors.

    SQL> show errors;
    Errors for PROCEDURE INT_SUBBASKETUPDSP:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/14 PLS-00103: Encountered the symbol "CASE" when expecting one of
    the following:
    ( * - + all mod null

    --------------------------------------------------------

    However, following works:
    SQL> CREATE OR REPLACE PROCEDURE INT_SUBBASKETUPDSP
    2 AS
    3 v_1 varchar2(20);
    4 v_sql varchar2(4000);
    5 BEGIN
    6 v_sql := 'select case when 1=1 then ''1''
    7 else ''2''
    8 end
    9 FROM dual ';
    10 execute immediate v_sql into v_1 ;
    11 dbms_output.put_line('The value is : '||v_1);
    12 END;
    13 /

    Procedure created.
    SQL> exec INT_SUBBASKETUPDSP;
    The value is : 1

    PL/SQL procedure successfully completed.
    --------------------------------------------------------

    Wierd database behaviour...plz help!

  2. #2
    Join Date
    May 2001
    Posts
    70
    You are not allowed to use Case statements in Stored Procedure unless you are in Oracle 9i.

    Good Luck

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Case in select statment --> part of SQL language (but no a PL/SQL language).
    There are 2 different languages.

    In 9i version of PL/SQL language CASE constraction in select support, in 8i not.

    When u wrote :
    5 select case when 1=1 then '1'
    6 else '2'
    7 end as "xyz"
    8 INTO v_1
    9 FROM dual;
    10 dbms_output.put_line('The value is : '||v_1);
    11 END;
    ...
    U used PL/SQL language.

    When u wrote :
    ...
    6 v_sql := 'select case when 1=1 then ''1''
    7 else ''2''
    8 end
    9 FROM dual ';
    10 execute immediate v_sql into v_1 ;
    ...
    U used SQL language.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width