Unix variables set from SQL Plus

Passing a parameter out of a SQL script to a UNIX variable. Method 1 outputs a literal. Method 2 will output a variable entered by a user.

Method 1

Create a file, "oranum.sql", which looks as follows:

set heading off
connect user/pwd@oraserver
select 10 from dual;
exit

run it as follows:

ORANUM=`sqlplus -s /nolog @oranum.sql`
echo $ORANUM

Method 2

#!/bin/ksh
echo "Enter value"
read val2
sqlplus $ORA_USER/$ORA_PWD@$ORA_HOST  << EOF1
set serveroutput on
begin
  dbms_output.put_line('${val2}');
end;
/
exit
EOF1

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

Generating dynamic spool files in SQL Plus

The following example will append YYYYMMDD_HHMISS to the spool file name.

set echo on
column spool_file new_val spool_file;
select 'dynamic_spool_file_' 
       || to_char(sysdate, 'yyyymmdd_hh24miss' ) spool_file 
from dual;
spool &spool_file
select 'file name : &spool_file' as spool_file_name from dual;
set echo off
spool off