cat a.sh
sqlplus etl_user/etl_user << !
set heading off
col flag noprint
column flag new_val dat
select odb2odm_flag flag from etl_process_ctrl;
host echo &dat > /data/odb2odm-ok.txt
exit;
exit;
!
cat a.sh
sqlplus ods_user/ods_user << !
set heading off
col today noprint
column today new_val dat
select to_char( sysdate, 'yyyy-mm-dd') today from dual;
If &dat ='2009-05-22' Then
begin
host echo &dat > /data/s.txt
end;
End If;
exit;
exit;
!
806803@CNHZPD-6VRWY1X ~/new_folder
$ str="2008-10-11"
806803@CNHZPD-6VRWY1X ~/new_folder
$ result=`sqlplus -s eps/eps@psportal <set heading off
select to_date('$str','YYYY-MM-DD') from dual;
exit;
eof
`
806803@CNHZPD-6VRWY1X ~/new_folder
$ echo $result
11-10? -08
$ result=`sqlplus -s eps/eps@psportal <set heading off
select 1 from dual union select 2 from dual;
exit;
eof
`
806803@CNHZPD-6VRWY1X ~/new_folder
$ echo $result
1 2
文章出处:http://www.diybl.com/course/7_databases/oracle/Oracleshl/200875/130521.html
Shell 与 SQLPLUS 的 交互 ---- 参数传递 返回值
unix上要执行oracle的sql,常见的是用shell脚本来调用sqlplus执行sql。
用这种方式,自然shell和sqlplus就有可能进行交互---shell传递参数进sqlplus,sqlplus将结果返回给shell。
从shell传参数进sqlplus是很简单的,可以在sql语句中用${shell变量名}的方式直接引用shell的变量.
eg,
transaction_sequence=$1
sqlplus -S stag_maint/stag_maint_ps@oraods << EOF
delete ODSDEFAULT_ODS_CONTROL WHERE TRANSACTION_SEQUENCE = ${transaction_sequence};
exit;
EOF
但如果我一个sql的执行结果需要返回给shell,那该怎么处理呢?shell中可没法直接来引用sqlplus里的变量。
经过研究,我发现以下两种方式可以达到这个目的。
方法1-----------
VALUE=`sqlplus -S stag_maint/stag_maint_ps@oraods << EOF
set heading off
set pagesize 0;
set feedback off;
set verify off;
set echo off;
select sysdate from dual;
exit;
END`
if [ -z "$VALUE" ]; then
echo "No rows returned from database"
exit 0
else
echo "Max Sequence Number: $VALUE"
fi
方法2-----------
sqlplus -S stag_maint/stag_maint_ps@oraods << EOF
set heading off feedback off pagesize 0 verify off echo off
col coun new_value v_coun
select 255 coun from dual;
exit v_coun
EOF
VALUE="$?"
echo "The number of rows is $VALUE."
不过方法2有个局限性,exit后面跟的数值只能在0-255之间。超过后会自动以255取余。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16396910/viewspace-1029556/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16396910/viewspace-1029556/