一、最简单的shell里调用sqlplus.

#!/bin/bash
sqlplus -S user/password /nolog > result.log <

set heading off

set feedback off

set pagesize 0

set verify off

set echo off
select * from tab;
exit;
EOF

二、把sqlplus执行结果传递给shell方法一

#!/bin/bash
VALUE=`sqlplus -S /nolog <

set heading off feedback off pagesize 0 verify off echo off numwidth 4
connect user/password
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

三、把sqlplus执行结果传递给shell方法二

注意sqlplus段使用 col .. new_value .. 定义了变量并带参数exit, 然后自动赋给了shell的$?

#!/bin/bash
sqlplus -S /nolog > result.log <

set heading off feedback off pagesize 0 verify off echo off numwidth 4
connect user/password
col coun new_value v_coun
select count(*) coun from tab;
exit v_coun;
EOF
VALUE="$?"
echo "The number of rows is $VALUE."

四、把shell程序参数传递给sqlplus

$1表示第一个参数, sqlplus里可以直接使用, 赋变量的等号两侧不能有空格不能有空格.

#!/bin/bash
NAME="$1"
sqlplus -S user/password <

select * from tab where tname = upper('$NAME');
exit;
EOF

五、为了安全要求每次执行shell都手工输入密码

#!/bin/bash
echo -n "Enter password for u_test:"
read PASSWD
sqlplus -S /nolog <

connect user/$PASSWD
select * from tab;
exit;
EOF

六、为了安全从文件读取密码

对密码文件设置权限, 只有用户自己才能读写.

$ echo 'password' > u_test.txt
$ chmod g-rwx,o-rwx u_test.txt
$ vi test6.sh

#!/bin/bash
PASSWD=`cat u_test.txt`
sqlplus -S /nolog <

connect user/$PASSWD
select * from tab;
exit;
EOF