Re: pl/sql forcing RHS of = in where clause to be variable?

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Wed, 02 Jul 2008 00:07:24 +0200
Message-ID: <g4e9n5$8p4$1@online.de>

mh_at_pixar.com schrieb:
> This code will compare a column to a variable:
>
> declare myx number;
> begin
> select * from foo where x=myx;
> end;
>
> but suppose I have a variable with the same name as a column:
>
> declare x number;
> begin
> select * from foo where x=x;
> end;
>
> This will compare column x with column x, and not with the
> variable x, right? Is there syntax to force the rightmost
> x to be a variable? Or should I do
>
> myx := x;
> select * from foo where x=myx;
>
> This is in the context of a parameter name of a public
> function, so I don't want to do the obvious choice
> of renaming the variable.
>
> Many TIA!
> Mark
>

Use a block with a label:

SQL> create table t(x number);

Table created.

SQL> insert into t values(42);

1 row created.

SQL> set serverout on
SQL> declare

   2 x number;
   3 d number;
   4 begin
   5 select * into d from t where x = x;    6 dbms_output.put_line('d = ' || to_char(d));    7 end;
   8 /
d = 42

PL/SQL procedure successfully completed.

SQL> begin

   2 <<my_label>>
   3 declare

   4      x number := 5;
   5      d number;
   6    begin
   7      select * into d from t where x = my_label.x;
   8      dbms_output.put_line('d = ' || to_char(d));

   9 end;
  10 end my_label;
  11 /
begin
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7

hth,
Urs Metzger Received on Tue Jul 01 2008 - 17:07:24 CDT