cat test.shsqlplus -S / as sysdba << ! set heading off col today noprint column today new_val dat select to_char( sysdate, 'yyyy-mm-dd') today from dual; host echo 'today is ' &dat exit; exit; !test.sh today is 2010-01-25
直接把他给打印出来就行了function GetTimeMargin {sqlplus /nolog <<_EOF_ |grep "###" >tmp.ini connect $userid set serveroutput on ; set head off; set linesize off; declare v_result number(12); begin select abs(ceil((to_date('$1','yyyymmddhh24miss') - to_date('$2','yyyymmddhh24miss'))*1440)) into v_result from dual; dbms_output.put_line('###'||v_result); end ; / _EOF_echo `more tmp.ini|awk '{print substr($0,index($0,"###")+3,length($0)-index($0,"###")-2)}'` rm tmp.ini }
set heading off
col today noprint
column today new_val dat
select to_char( sysdate, 'yyyy-mm-dd') today from dual;
host echo 'today is ' &dat
exit;
exit;
!test.sh
today is 2010-01-25
表已创建。shell命令有执行结果状态值,sqlplus没做过这样的尝试,不晓得怎么捕捉。
sql>spool test.log
sql>....
sql>spool off
{sqlplus /nolog <<_EOF_ |grep "###" >tmp.ini
connect $userid
set serveroutput on ;
set head off;
set linesize off;
declare
v_result number(12);
begin
select abs(ceil((to_date('$1','yyyymmddhh24miss') - to_date('$2','yyyymmddhh24miss'))*1440))
into v_result from dual; dbms_output.put_line('###'||v_result);
end ;
/
_EOF_echo `more tmp.ini|awk '{print substr($0,index($0,"###")+3,length($0)-index($0,"###")-2)}'`
rm tmp.ini
}