Re: why does sub select in DECODE not work in a procedure

<randall2n_at_yahoo.com> wrote in message
news:1151097336.219695.299850_at_r2g2000cwb.googlegroups.com...
:I am not sure if I am doing something wrong or if it is a "feature" in
: Oracle.
:
: I created a rather long and complex SQL statement using decode, but
: once I put it inside a procedure I get an error about the SELECT inside
: the decode. It runs perfectly fine with out the procedure but I need
: it inside a stored function.
:
: I made the SQL into the most basic I could think of that reproduces the
: behavior. So the information is obviously junk, the concept is fairly
: similar, but shoudl be able to run it from any Oracle Db.
:
: Oracle Version: 8i (8.1.7.4.0)
:
: Any information or assistance apreciated.
:
: SQL/procedure/error are below:
: --------------------------------------------------------------------------
: SQL:works fine:
: ---------------------
: SELECT DECODE( TO_CHAR(SYSDATE,'MON'),
: (SELECT USER FROM DUAL), 'MATCH',
: 'NO MATCH' )
: FROM DUAL;
:
: --------------------------------------------------------------------------
: Procedure:throws error:
: ---------------------
: DECLARE
: TEMP VARCHAR2(50);
: BEGIN
: SELECT DECODE( TO_CHAR(SYSDATE,'MON'),
: (SELECT USER FROM DUAL), 'MATCH',
: 'NO MATCH' )
: INTO TEMP
: FROM DUAL;
: DBMS_OUTPUT.put_line( TEMP );
: END;
:
: --------------------------------------------------------------------------
: Error:
: ---------------------
: ERROR at line 5:
: ORA-06550: line 5, column 19:
: PLS-00103: Encountered the symbol "SELECT" when expecting one of the
: following:
: ( - + 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>
: ORA-06550: line 5, column 41:
: PLS-00103: Encountered the symbol "," when expecting one of the
: following:
: ; return returning and or
: ORA-06550: line 7, column 5:
: PLS-00103: Encountered the symbol "INTO" when expecting one of the
: following:
: ;
:

works fine in 10.2.0.1.0 and 9.2.0.7.0 -- apparently scalar subqueries are not supported in PL/SQL in 8.1.7.

any possibility of upgrading?

++ mcs Received on Fri Jun 23 2006 - 16:35:23 CDT