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.
Again, for complete examples of passing complex variable to SQL*Plus
from a shall script, get the code download in "Oracle
Shell Scripting".
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2012
All rights reserved.
Oracle ©
is the registered trademark of Oracle Corporation.
|
|
