the empty quarter

To prompt the user for a value when running SQL statements in SQL*Plus use the & symbol. The query below will prompt for variable “name” twice:

select 'Hello ' || '&name' from dual
union all
select 'Goodbye ' || '&name' from dual;

To prompt for any given variable only once and use the entered value multiple times use two ampersands instead of one. The query below will prompt for the variable “name” once only:

select 'Hello ' || '&&name' from dual
union all
select 'Goodbye ' || '&&name' from dual;

To prevent prompting altogeher, use the set define command:

set define off
select 'Hello world & dog' from dual;