ksh脚本调用sqlplus查询返回0但数据库直查返回6问题排查

ksh脚本内嵌sqlplus执行固定计数查询，手动在库内执行返回6，脚本执行无报错但返回0，核心排查方向与修复方案如下：

最高概率原因：连接环境不一致 #

• 脚本开头加载的setenv.ksh会重置ORACLE_SID、TNS_ADMIN、ORACLE_HOME等Oracle连接环境变量，sqlplus username/password@$ORACLE_SID会根据当前环境的tns配置解析连接串，极大概率连到了和你手动登录不一样的测试/备用实例，该实例下目标表对应日期的数据量恰好为0，因此无报错但结果不符。
• 若脚本配置的连接用户和你手动执行SQL的用户不一致，还可能命中该用户下的同名私有表/私有同义词，指向无数据的测试表。

快速验证方法 #

在内嵌SQL中加入以下两行校验语句，执行后即可确认连接是否正确：

-- 输出当前连接的数据库名
select sys_context('USERENV','DB_NAME') from dual;
-- 输出当前查询的表所属schema
select owner from all_objects where object_name = 'YOUR_REAL_TABLE_NAME' and object_type='TABLE';

将输出结果和你手动执行SQL时的库名、表schema对比，即可确认是否连错库/查错表。

脚本本身存在的缺陷 #

即使连接正确，现有脚本也存在多个会导致结果异常的问题：

• 变量定义语法错误：DATE=date "+%m%d%Y 行缺少闭合反引号，会导致shell解析逻辑异常；最后赋值COUNT变量的行export COUNT=echo $returnMessage" 同样缺少闭合反引号，且未过滤sqlplus输出的冗余内容，根本无法拿到正确的计数值。
• sqlplus未开静默模式，加了SET ECHO ON，会把所有SQL>提示符、执行的命令文本都输出到返回结果中，干扰值提取。
• 开启spool后未加spool off，会导致spool文件内容缓冲不完整，极端情况下可能影响输出。
• here-document使用<< EOF未加单引号包裹EOF，shell会对SQL块内的$、反引号等特殊字符做变量替换，后续如果SQL里加了绑定变量很容易出现语法异常。

修复后可直接使用的脚本 #

#!/bin/ksh
. /apps/path/config/setenv.ksh
DATE=`date "+%m/%d/%Y"`

# 加-s参数进入sqlplus静默模式，自动屏蔽版本头、SQL>提示符等冗余输出
returnMessage=`sqlplus -s username/password@$ORACLE_SID << 'EOF'
WHENEVER OSERROR EXIT FAILURE;
WHENEVER SQLERROR EXIT FAILURE;

SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
SET PAGES 0
SET LINESIZE 90

spool /apps/path/data/test.txt

-- 排查阶段保留这两行校验，确认连接正确后可删除
select sys_context('USERENV','DB_NAME') from dual;
select owner from all_objects where object_name = 'YOUR_REAL_TABLE_NAME' and object_type='TABLE';

select count(*) from your_real_table_name where dt = to_date('06/18/2020','MM/DD/YYYY');

spool off
exit
EOF
`
exitCode=$?
oracleError=`echo "$returnMessage" | grep -i ORA-`
if [ -n "$oracleError" -o "$exitCode" -ne 0 ]; then
    log "An error occurred while looking up the count"
    log "SQLPlus Exit Code = $exitCode"
    log "SQLPlus Message is: $returnMessage"
    return 1
fi
# 过滤所有非数字行，取最后一个数字值即为count结果，避免冗余输出干扰
export COUNT=`echo "$returnMessage" | grep -E '^[0-9]+$' | tail -1`
return 0

内容的提问来源于stack exchange，提问作者Yasasvi Sunkara