shell查询中文数据ora-01756_shell调用sqlplus查询oracle

[oracle@hb shell_test]$ cat echo_time

#!/bin/sh

一.最简单的调用sqlplus

sqlplus -S "sys/unimas as sysdba" << !

select to_char(sysdate,'yyyy-mm-dd') today from dual;

exit;

!

[oracle@hb shell_test]$ ./echo_time

TODAY

----------

2011-03-21

-S 是silent mode，不输出类似“SQL>”，连接数据库，关闭数据库之类的信息。

eof可以是任何字符串 比如"laldf"那么当你输入单独一行laldf时"shell认为输入结束，但是必须表示块开始必须使用<

开始和结束要匹配这个符号“<

举例子:

[oracle@hb shell_test]$ sqlplus -s "sys/unimas as sysdba" << abc

> select to_char(sysdate,'yyyy-mm-dd') today from dual;

> exit;

> abc

TODAY

----------

2011-03-21

二.sqlplus的结果传递给shell的方法一

[oracle@hb shell_test]$ cat test2.sh

#!/bin/bash

VALUE=`sqlplus -S "test/unimas" << !

set heading off

set feedback off

set pagesize 0

set verify off

set echo off

select to_char(sysdate,'yyyy-mm-dd') today from dual;

exit

!`

echo $VALUE

if [ -n "$VALUE" ]; then

echo "The rows is $VALUE"

exit 0

else

echo "There is no row"

fi

三.sqlplus的结果传递给shell的方法二

[oracle@hb shell_test]$ cat test1.sh

#!/bin/bash

sqlplus -S "test/unimas" << !

set heading off

set feedback off

set pagesize 0

set verify off

set echo off

col coun new_value v_coun

select count(*) coun from lesson;

exit v_coun

!

VALUE="$?"

echo "show row:$VALUE"

col coun new_value v_coun v_coun为number类型。因为exit 只能返回数值类型。

四.把shell参数传递给sqlplus

#!/bin/bash

t_id="$1"

sqlplus -S "test/unimas" << !

set heading off

set feedback off

set pagesize 0

set verify off

set echo off

select teachername from teacher where id=$t_id;

exit

!

五.sqlplus的结果存储在文件中

#!/bin/sh

sqlplus -S "test/unimas"<

set heading off

set feedback off

set pagesize 0

set verify off

set echo off

spool spool_file

SELECT * from teacher;

spool off

exit;

EOF

####################################################################################################################################

查看调度系统状态脚本：

#!/bin/sh

if [[ -z "$1" ]] || [[ "$1" -ne 0 && "$1" -ne 2 ]]       #使用[[ ]] 进行逻辑短路操作

then

echo "Please input your parameter: query status[0,2]!"

exit

fi

#for buname in cnlog enlog ItLog JrLog AuLog InnerLog

for buname in cnlog enlog

do

sqlplus -S 'etl/etl@dw_testdb' << abc        #使用 << EOF方式输入信息    set line 155

set pages 9999

SELECT /*+ PARALLEL(a,4) */ * FROM $buname.hla_job_rec a where status = $1;

exit

abc

done