shell 调用 sqlplus 各种情况示例

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]$