Using UNIX environment variables in SQL Plus

Passing variables to a SQL script under UNIX enviroment.

Method 1

$ Variable1="Someting value"
sqlplus db_user/db_password@db_sid <<EOF
set serveroutput on
BEGIN
  dbms_output.put_line('${Variable1}');
END;
/
exit;
EOF



Method 2
Create a test file called test_script.sql

$ cat test_script.sql
set serveroutput on
DECLARE
  var1 varchar2(100):='&&1';
  var2 varchar2(100):='&&2';
BEGIN
  dbms_output.put_line(var1);
  dbms_output.put_line(var2);
END;
/
exit;

For the test purpose let’s create a UNIX script file which executes out sql script.
$ cat test_unix_script.sh
var1="Some text"
var2="Some other text”
sqlplus -s db_user/db_password@db_sid @test_script.sql $var1 $var2

Output from execution of  the script
$ test_str.sh
old   3:   l_var1 varchar2(100):='&&1';
new   3:   l_var1 varchar2(100):='Some text';
old   4:   l_var2 varchar2(100):='&&2';
new   4:   l_var2 varchar2(100):='Some other text';

Some text 
Some other text