Re: PL/SQL, subqueries and expressions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 29 Apr 1999 15:24:55 GMT
Message-ID: <372a7984.11899140@192.86.155.100>

A copy of this was sent to claggett_at_my-dejanews.com (if that email address didn't require changing) On Thu, 29 Apr 1999 02:14:25 GMT, you wrote:

>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 ;)
>

that would be a bug yes. I'll file one. It appears plsql doesn't understand the new ability to use a select where ever you could use a 'column' or 'expression' before (didn't exist before 8i). Here is the test case and the workaround:

SQL> create table mytable ( value int );

Table created.

SQL>
SQL> UPDATE mytable

  2      SET Value = Value
  3      WHERE Value = Value +
  4         (SELECT Max(Value) FROM mytable) *
  5         (SELECT Min(Value) FROM mytable)

  6 /

0 rows updated.

SQL>
SQL> begin
  2 UPDATE mytable

  3      SET Value = Value
  4      WHERE Value = Value +
  5         (SELECT Max(Value) FROM mytable) *
  6         (SELECT Min(Value) FROM mytable);

  7 end;
  8 /

       (SELECT Max(Value) FROM mytable) *
        *

ERROR at line 5:
ORA-06550: line 5, column 9:
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>

SQL>
SQL> begin
  2 execute immediate
  3 'UPDATE mytable

  4      SET Value = Value
  5      WHERE Value = Value +
  6         (SELECT Max(Value) FROM mytable) *
  7         (SELECT Min(Value) FROM mytable)';

  8 end;
  9 /

PL/SQL procedure successfully completed.

>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
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Apr 29 1999 - 10:24:55 CDT