http://hi.baidu.com/edeed/blog/item/291698228a5694f4d7cae2c1.html/cmtid/e87926977f74636155fb968f
测试平台：RHEL4.5
一、最简单的shell里调用sqlplus.
$ vi test1.sh
#!/bin/bash
sqlplus -S /nolog > result.log <<EOF
set heading off feedback off pagesize 0 verify off echo off
conn u_test/iamwangnc
select * from tab;
exit
EOF
$ chmod +x test1.sh
$ ./test1.sh
二、把sqlplus执行结果传递给shell方法一
注意sqlplus段使用老板键`了, 赋变量的等号两侧不能有空格.
$ vi test2.sh
#!/bin/bash
VALUE=`sqlplus -S /nolog <<EOF
set heading off feedback off pagesize 0 verify off echo off numwidth 4
conn u_test/iamwangnc
select count(*) from tab;
exit
EOF`
if [ "$VALUE" -gt 0 ]; then
echo "The number of rows is $VALUE."
exit 0
else
echo "There is no row in the table."
fi
$ chmod +x test2.sh
$ ./test2.sh
三、把sqlplus执行结果传递给shell方法二
注意sqlplus段使用 col .. new_value .. 定义了变量并带参数exit, 然后自动赋给了shell的$?
$ vi test3.sh
#!/bin/bash
sqlplus -S /nolog > result.log <<EOF
set heading off feedback off pagesize 0 verify off echo off numwidth 4
conn u_test/iamwangnc
col coun new_value v_coun
select count(*) coun from tab;
exit v_coun
EOF
VALUE="$?"
echo "The number of rows is $VALUE."
$ chmod +x test3.sh
$ ./test3.sh
四、把shell程序参数传递给sqlplus
$1表示第一个参数, sqlplus里可以直接使用, 赋变量的等号两侧不能有空格不能有空格.
$ vi test4.sh
#!/bin/bash
NAME="$1"
sqlplus -S u_test/iamwangnc <<EOF
select * from tab where tname = upper('$NAME');
exit
EOF
$ chmod +x test4.sh
$ ./test4.sh ttt
五、为了安全要求每次执行shell都手工输入密码
$ vi test5.sh
#!/bin/bash
echo -n "Enter password for u_test:"
read PASSWD
sqlplus -S /nolog <<EOF
conn u_test/$PASSWD
select * from tab;
exit
EOF
$ chmod +x test5.sh
$ ./test5.sh
六、为了安全从文件读取密码
对密码文件设置权限, 只有用户自己才能读写.
$ echo 'iamwangnc' > u_test.txt
$ chmod g-rwx,o-rwx u_test.txt
$ vi test6.sh
#!/bin/bash
PASSWD=`cat u_test.txt`
sqlplus -S /nolog <<EOF
conn u_test/$PASSWD
select * from tab;
exit
EOF
$ chmod +x test6.sh
$ ./test6.sh
--End--
==========================================
试试
[sms@ydqxyy07 zxx]$ cat t_shell4.sh
#!/bin/bash
source /etc/profile
source ~/.bash_profile
THE_DATE=`date +"%Y-%m-%d %H:%M:%S"`
THE_DATE1=`date +"%H:%M:%S"`
THE_PATH=/home/sms/zxx
T_PATH="/home/sms/"
echo "[$THE_DATE]:Begin Start t_shell ......" >> /home/sms/zxx/log/t_shell.log
VALUE=`sqlplus -S ydqxn/ydqxn@ydqx <<eof
set pagesize 0 feedback off verify off heading off echo off
define P=$THE_DATE;
define P1=$THE_DATE1;
define PT=$THE_PATH;
define PT1=$T_PATH;
select '&P','&P1',to_date('&P1','HH24:mi:ss'),to_char(to_date('&P1','HH24:mi:ss'),'HH24miss'),'&PT','&PT1' from dual;
select '&P' from dual;
execute PRO_test('&PT','&P');
execute pro_test('&PT1','&P1');
exit
END`
if [ -z "$VALUE" ]; then
echo "No rows returned from database"
exit 0
else
echo "Some Message: $VALUE"
fi
[sms@ydqxyy07 zxx]$ ./t_shell4.sh
Some Message: 2011-04-22 12:59:38 01-APR-11 125938 /home/sms/zxx /home/sms/
2011-04-22
[sms@ydqxyy07 zxx]$ cat t_shell.sh
#!/bin/bash
source /etc/profile
source ~/.bash_profile
THE_DATE=`date +"%Y-%m-%d %H:%M:%S"`
THE_PATH=/home/sms/zxx
T_PATH="/home/sms/"
echo "[$THE_DATE]:Begin Start t_shell ......" >> /home/sms/zxx/log/t_shell.log
sqlplus ydqxn/ydqxn@ydqx <<eof
define P=$THE_DATE;
define PT=$THE_PATH;
define PT1=$T_PATH;
select '&PT','&P','&PT1' from dual;
execute PRO_test('&PT','&P');
execute pro_test('&PT1','&P');
exit
eof
THE_DATE1=`date +"%Y-%m-%d %H:%M:%S"`
echo "[$THE_DATE1]:End Start t_shell ......" >> /home/sms/zxx/log/t_shell.log
[sms@ydqxyy07 zxx]$ ./t_shell.sh
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 22 13:00:46 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> SQL> SQL> SQL> old 1: select '&PT','&P','&PT1' from dual
new 1: select '/home/sms/zxx','2011-04-22','/home/sms/' from dual
'/HOME/SMS/ZX '2011-04-2 '/HOME/SMS
------------- ---------- ----------
/home/sms/zxx 2011-04-22 /home/sms/
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[sms@ydqxyy07 zxx]$