How to use UNIX environment variables in SQL Plus

Passing a variable to a SQL script in UNIX.

Method 1

SOME_STR="Some Str"
sqlplus apps/password@oracle_sid <<EOF
set serveroutput on
begin
  dbms_output.put_line('${SOME_STR}');
end;
/
exit
EOF

Method 2

Create a test file, test_str.sql, to test with first.

$ more test_str.sql
set serveroutput on
declare

  l_var1 varchar2(100):='&&1';
  l_var2 varchar2(100):='&&2';

begin
  dbms_output.put_line(l_var1);
  dbms_output.put_line(l_var2);
end;
/
exit

Now for the test

$ more test_str.ksh
VAR1="Accounts Payable"
VAR2="Receivable"
sqlplus -s apps/password@oracle_sid @test_str.sql $VAR1 $VAR2


$ . test_str.ksh
old   3:   l_var1 varchar2(100):='&&1';
new   3:   l_var1 varchar2(100):='Account Payable';
old   4:   l_var2 varchar2(100):='&&2';
new   4:   l_var2 varchar2(100):='Receivable';

Accounts Payable
Receivable