randall2n_at_yahoo.com wrote:
> 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:
> ;
No problems on 9.2.0.6 and 10.2.0.1 on XP.
I guess 8.x didn't support Select as column in the cursor.
Thomas
Received on Fri Jun 23 2006 - 16:35:38 CDT
