Question:
Within
SQL*Plus
is there a way to get this value into a shell script environment
variable?
How do I pass a variable to SQL*Plus in a ksh shell script?
Answer: Passing variables to shell scripts is tricky! Jon
Emmons as a great book "Oracle
Shell Scripting" with lots of working examples of passing user
defined variables to Oracle in a shell script. Working scripts
exist in the books code download.
Also see
passing variables to SQL*Plus.
I used numbered variables, like &1 and &2, but as long as they are defined
and exported in the shell script, they will be resolved in SQL*Plus
(unless they contain special characters).
In cases where a variable contains special characters ($owner), you
have to "escape" variables in sqlplus when they contain dollar signs
. . .
If you do it all one line (a requirement in Windows shell scripting), it's
easy to pass a ksh shell script variable into sqlplus:
#!/bin/ksh
$min_snap=421
export $min_snap
$max_snap=426
export $max_snap
$ORACLE_HOME/bin/sqlplus
-S perfstat/perf$ORACLE_SID@$ORACLE_SID @sppurge $min_snap $max_snap
Here are some working examples using the C shell:
#!/bin/sh
lookup=$1
sqlplus -S system/manager << EOF
SELECT username, account_status, expiry_date
FROM dba_users
WHERE lower(username)=lower('$lookup');
exit;
EOF
Shell Variables and Embedded SQL
Earlier in this chapter, we saw how SQL can be
embedded in a shell script. One side benefit to this method is
that we can use shell variables to represent values within the
embedded SQL. In the following user_info.sh script we
pass a username to the script as an argument, it is transferred to a
variable named lookup for better readability and the variable lookup
is used in the WHERE clause within the SQL.
#!/bin/sh
lookup=$1
sqlplus -S system/manager << EOF
SELECT username, account_status, expiry_date
FROM dba_users WHERE lower(username)=lower('$lookup');
exit;
EOF
The result is a single script which can take an
argument and execute SQL using the value of that argument.
This can be a very efficient way to load data into Oracle tables
without the need for a load file or other intermediate step.
Here is another example where the entire
SQL*Plus session is passed as a single variable called
invalid_count:
invalid_count=`sqlplus -L -s / as sysdba <<.eof.
set pages 0 feed off heading off echo off verify off termout off
select count(*) from dba_objects where status != 'VALID';
exit
.eof.`
echo $invalid_count
In the code above, note that the shell
script accepts the SQL*Plus output via the use of the "grave
(`)" symbol.
Again, for complete examples of passing complex variable to SQL*Plus
from a shell script, get the code download in "Oracle
Shell Scripting".
