Here are some simple examples tested on Oracle 11.2. You can use a variable beginning with one ampersand:
SQL> !cat accept1.sql
begin
dbms_output.put_line('Hello &your_name');
end;
/
If you do this, you are asked to input the data again when you rerun the script:
SQL> set echo on
SQL> set serveroutput on
SQL> @accept1
SQL> begin
2 dbms_output.put_line('Hello &your_name');
3 end;
4 /
Enter value for your_name: Andrew
old 2: dbms_output.put_line('Hello &your_name');
new 2: dbms_output.put_line('Hello Andrew');
Hello Andrew
PL/SQL procedure successfully completed.
SQL> @accept1
SQL> begin
2 dbms_output.put_line('Hello &your_name');
3 end;
4 /
Enter value for your_name: Brian
old 2: dbms_output.put_line('Hello &your_name');
new 2: dbms_output.put_line('Hello Brian');
Hello Brian
PL/SQL procedure successfully completed.
SQL>
You can use a variable beginning with two ampersands:
SQL> !cat accept2.sql
begin
dbms_output.put_line('Hello &&first_name');
end;
/
If you do this, PL/SQL remembers the value of the variable between one execution and the next:
SQL> @accept2
SQL> begin
2 dbms_output.put_line('Hello &&first_name');
3 end;
4 /
Enter value for first_name: Colin
old 2: dbms_output.put_line('Hello &&first_name');
new 2: dbms_output.put_line('Hello Colin');
Hello Colin
PL/SQL procedure successfully completed.
SQL> @accept2
SQL> begin
2 dbms_output.put_line('Hello &&first_name');
3 end;
4 /
old 2: dbms_output.put_line('Hello &&first_name');
new 2: dbms_output.put_line('Hello Colin');
Hello Colin
PL/SQL procedure successfully completed.
SQL>
Later, if you want to provide a new value, you can UNDEFINE the variable between executions:
SQL> undefine first_name
SQL> @accept2
SQL> begin
2 dbms_output.put_line('Hello &&first_name');
3 end;
4 /
Enter value for first_name: David
old 2: dbms_output.put_line('Hello &&first_name');
new 2: dbms_output.put_line('Hello David');
Hello David
PL/SQL procedure successfully completed.
SQL> undefine first_name
SQL> @accept2
SQL> begin
2 dbms_output.put_line('Hello &&first_name');
3 end;
4 /
Enter value for first_name: Elvis
old 2: dbms_output.put_line('Hello &&first_name');
new 2: dbms_output.put_line('Hello Elvis');
Hello Elvis
PL/SQL procedure successfully completed.
SQL>
You can accept the variable beforehand in SQL*Plus as follows:
SQL> !cat accept3.sql
accept christian_name prompt "Who are you? "
begin
dbms_output.put_line('Hello &christian_name');
end;
/
SQL> @accept3
SQL> @accept3
SQL> accept christian_name prompt "Who are you? "
Who are you? Finbar
SQL> begin
2 dbms_output.put_line('Hello &christian_name');
3 end;
4 /
old 2: dbms_output.put_line('Hello &christian_name');
new 2: dbms_output.put_line('Hello Finbar');
Hello Finbar
PL/SQL procedure successfully completed.
SQL>
If you don’t want Oracle to display the old and new values of the variable, you can stop this happening by running set verify off beforehand:
SQL> set verify off
SQL> @accept3
SQL> accept christian_name prompt "Who are you? "
Who are you? Gordon
SQL> begin
2 dbms_output.put_line('Hello &christian_name');
3 end;
4 /
Hello Gordon
PL/SQL procedure successfully completed.
SQL>
