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

--
Mark Harrison
Pixar Animation Studios

mh@pixar.com schrieb:
> 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.

I don't think, you have too many choices.

Best regards

Maxim

mh@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

Urs Metzger schrieb:
> mh@pixar.com schrieb:
> Use a block with a label:
> Urs Metzger

This could do the job for regular plsql variables, but if the actual one
is a function parameter, i think, you *have* to reassign.

Best regards

Maxim

Maxim Demenko schrieb:
> Urs Metzger schrieb:
>> mh@pixar.com schrieb:
>> Use a block with a label:
>> Urs Metzger
>
> This could do the job for regular plsql variables, but if the actual one
> is a function parameter, i think, you *have* to reassign.
>
> Best regards
>
> Maxim

I should correct myself, it seems, parameter can be referenced with the
name of code unit...

SQL> create or replace function abc(empno number) return varchar2 is
2 l_ename varchar2(20);
3 begin
4 select ename into l_ename from emp where empno = abc.empno;
5 return l_ename;
6 end;
7 /

Function created.

SQL> select abc(7788) from dual;

ABC(7788)
-----------------------------------------------------------------------------------------------------------------------------
SCOTT


Best regards

Maxim

Maxim Demenko <mdemenko@gmail.com> wrote:
> 4 select ename into l_ename from emp where empno = abc.empno;

Ah, this is just what I was hoping for.
Thank you so much Maxim!

--
Mark Harrison
Pixar Animation Studios

mh@pixar.com schrieb:
> Maxim Demenko <mdemenko@gmail.com> wrote:
>> 4 select ename into l_ename from emp where empno = abc.empno;
>
> Ah, this is just what I was hoping for.
> Thank you so much Maxim!
>

You are welcome, just for the record, all that can be found at usual
place ;-)

http://download.oracle.com/docs/cd/B....htm#sthref431

Best regards

Maxim