why does sub select in DECODE not work in a procedure

From: <randall2n_at_yahoo.com>
Date: 23 Jun 2006 14:15:36 -0700
Message-ID: <1151097336.219695.299850@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:

; Received on Fri Jun 23 2006 - 16:15:36 CDT