I have decode statement in a query. When the query
is executed its works fine. If its put in a stored procedure
it gives the following error :
5/17 PLS-00103: Encountered the symbol "SELECT" when expecting one of
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
Any idea what the problem could be.
select decode((select 1 from dual),1,sysdate,sysdate -1) from dual;
create or replace procedure abcd
select decode((select 1 from dual),1,sysdate,sysdate -1) into a from dual;
The problem is not the DECODE, it is the "scalar subquery" (select 1 from dual) which is not allowed by your PL/SQL engine.
The PL/SQL engine's SQL syntax has always lagged behind the SQL engine's syntax, and it happens that in your Oracle version scalar subqueries have been added to the SQL engine but are not recognized by the PL/SQL engine.
Assuming that an upgrade to 9i or 10G is not an option, you have various options:
1) rewrite without the scalar subquery
2) put the query into a view
3) use dynamic SQL