-
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!
-
You are not allowed to use Case statements in Stored Procedure unless you are in Oracle 9i.
Good Luck
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|