shell脚本接收存储过程返回值

shell获取存储过程的返回值
一:shell.sql文件
var O_strReturn varchar2;
call DND_IGMethod_Search2_SP(‘DEM’,’HAM’,:O_strReturn);
SELECT ‘return|’||:O_strReturn FROM dual;
commit;
exit;
二:shell.sh
!/bin/bash
HOSTNAME=hostname
PATH=$PATH;export PATH; ORACLE_BASE=”/u01/app/oracle”; export ORACLE_BASE; ORACLE_HOME=”/u01/app/oracle/product/11.2.0.4/dbhome_1”; export ORACLE_HOME; BASE_DIR=/home/oracle/data/bcp JOB_TXT=$BASE_DIR/shell.txt
JOB_SQL=$BASE_DIR/shell.sql
MAILETO=”邮箱@qq.com”

$ORACLE_HOME/bin/sqlplus -s 账户/密码@//ip地址/oracl @$BASE_DIR/shell.sql >$JOB_TXT

mydate=cat $JOB_TXT |grep "return"| awk -F "|" '{print $2}'
echo $mydate
2还有一种方法(将存储过程写在脚本里面)
一shell.sh
!/bin/bash
HOSTNAME=hostname
PATH=$PATH;export PATH; ORACLE_BASE=”/u01/app/oracle”; export ORACLE_BASE; ORACLE_HOME=”/u01/app/oracle/product/11.2.0.4/dbhome_1”; export ORACLE_HOME; BASE_DIR=/home/oracle/data/bcp JOB_TXT=$BASE_DIR/shell.txt
JOB_SQL=$BASE_DIR/shell.sql
MAILETO=”邮箱@qq.com”

value=$ORACLE_HOME/bin/sqlplus -s I账户/密码@//ip地址/oracl << EOF
set heading off
set feedback off
set pages 0
set trimspool on
set serverout off
var O_strReturn VARCHAR2;
var I_strRegion varchar2;
var I_strOffice varchar2;
call DND_IGMethod_Search2_SP('DEM','HAM',:O_strReturn);
select :O_strReturn from dual;
exit;
EOF
echo $value

shell发送邮件
cat $JOB_TASK2_EXIST | mailx -s 'iEQP WARNING Another system task monitor job is still running'${MAIL_TO} – -r ${HOSTNAME}@coscon.com
指定发件人 和收件人主题,内容