PL/SQL, subqueries and expressions

From: <claggett_at_my-dejanews.com>
Date: Thu, 29 Apr 1999 02:14:25 GMT
Message-ID: <7g8f9v$bs4$1@nnrp1.dejanews.com>

Hello,

This (admittedly nonsensical) statement below works fine when I run it as pure SQL. However, place a PL/SQL block around it and Oracle produces a syntax error as shown below. Any suggestions as to what I am doing wrong? My wild guess is that PL/SQL is not aware of subqueries within an expression. Told you it was a wild guess ;)

For the record, I am working with Oracle 8.1.5i.

PS sorry for the incomplete posts earlier (I'm learning a new news reader).

• SQL Query:

  UPDATE mytable
    SET Value = Value
    WHERE Value = Value +

       (SELECT Max(Value) FROM mytable) *
       (SELECT Min(Value) FROM mytable)

• PL/SQL Block:

  BEGIN
    UPDATE mytable

      SET Value = Value
      WHERE Value = Value +
         (SELECT Max(Value) FROM mytable) *
         (SELECT Min(Value) FROM mytable);

  END;

• Syntax Error:

  ERROR at line 5:
  ORA-06550: line 5, column 10:
  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 cast trim forall


             <a string literal with character set specification>


             <a number> <a single-quoted SQL string>

  ORA-06550: line 5, column 36:
  PLS-00103: Encountered the symbol "*" when expecting one of the following:

<an identifier> <a double-quoted delimited-identifier> set

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Apr 28 1999 - 21:14:25 CDT