Friday, 23 April 2010

Substitution and bind variables

Substitution and bind variables in execute script f5.

(execute statement f9 handles substitution variables '&' the same , but prompts for bind variables ':' in select update delete... (but not anonymous blocks))

Input



undefine a_value
undefine fred
--Substitution variables ie &...
--prompt if not known and set
prompt &&a_value
--set a value explicitly
define new_value = fred
prompt &fred

--Bind variables ie :...
variable my_bind varchar2
begin
:my_bind := 'value';
end;
/
begin
null;
--(use :my_bind within an anonymous block);
end;
/
--or
print :my_bind




Output


> undefine a_value

> undefine fred

> --Substitution variables ie &...

> --prompt if not known and set

> prompt &&a_value

old:prompt &&a_value
new:prompt set_a_value
set_a_value
> --set a value explicitly

> define new_value = fred

> prompt &fred

old:prompt &fred
new:prompt set_fred
set_fred
> --Bind variables ie :...

> variable my_bind varchar2

> begin
:my_bind := 'value';
end;

anonymous block completed
> begin
null;
--(use :my_bind within an anonymous block);
end;

anonymous block completed
> --or

> print :my_bind

MY_BIND
-----
value

1 comment:

林惠萍 said...
This comment has been removed by a blog administrator.