there are 2 ways to do this, both require you do something like:

ops$tkyte@8i> column x new_val my_x

that says "when I run a query that selects a column named X put the value of the last row fetched into a define variable called MY_X".

Now, if I have a function I can "select" (eg: it does not write to the database state -- no inserts/updates/deletes) I can simply:


ops$tkyte@8i> create or replace function foo( a in number ) return number
2 as
3 begin
4 return a*2;
5 end;
6 /
Function created.

ops$tkyte@8i> select foo(10) X from dual;

X
----------
20

ops$tkyte@8i>
ops$tkyte@8i> select &my_x from dual;
old 1: select &my_x from dual
new 1: select 20 from dual

20
----------
20




select that function from dual to run it and get the value into the define variable. If I cannot select foo() from dual -- I can still do this using a bind variable:

ops$tkyte@8i> variable bind_var number
ops$tkyte@8i> exec :bind_var := foo( 22 )

PL/SQL procedure successfully completed.

ops$tkyte@8i> select :bind_var X from dual;

X
----------
44

ops$tkyte@8i>
ops$tkyte@8i> select &my_x from dual;
old 1: select &my_x from dual
new 1: select 44 from dual

44
----------
44


So I can always select BIND_VARIABLE "X" from dual; and that'll do it for me.