Wednesday, December 11, 2013

How to Provide User Input to PL/SQL

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

No comments: