无法通过shell执行.sql来生成csv文件

Question

我试图通过调用。sql文件生成csv。问题是在执行时正在创建一个空文件。对DB运行sql将返回记录。抱歉，如果这已经回答了，但我无法找到任何解决我的问题的答案。

FILE="CASE.csv"
export dest_loc=/dest/loc
export LOGFILE=$LOG/100.$NOW_TS.log
echo "$0 started at $(date +"%Y-%m-%d:%H:%M:%S")" > ${LOGFILE}
sqlplus -s $DB_LOGIN<<EOSQL>>${LOGFILE}
SET echo OFF
SET feedback OFF    
SET sqlprompt ''    
SET TERMOUT OFF
SET UNDERLINE OFF
SET trimspool ON
SET trimout ON
SET SQLBLANKLINES ON
SET PAGESIZE 50000
SET COLSEP "|"
SET LINESIZE 650
SPOOL $dest_loc/$FILE
@$SRC/sql_to_run.sql
SPOOL OFF
EOSQL
echo "nChecking for ORACLE errors..."                            >> ${LOGFILE}
grep 'ORA-' $LOGFILE                                              >> ${LOGFILE}
rc=$?
if [ ${rc} = 0 ];
then
echo "nThere has been an ORACLE error. RC = $RC"   >> ${LOGFILE}
echo "n$0 ended at $(date +"%Y-%m-%d:%H:%M:%S")"   >> ${LOGFILE}
echo "####################################"  >> ${LOGFILE}
exit 2
else 
echo "nNo ORACLE errors found."   >> ${LOGFILE}
fi

捕获的日志文件

./100.sh started at 2021-10-20:20:14:22 Checking for ORACLE errors…没有发现ORACLE错误。

Answer 1

csv文件为空的原因可能是因为您有一个没有捕获的错误。sqlplus是一个二进制程序，因此它将始终以0退出，除非您使用whenever sqlerror和whenever oserror来捕获执行过程中产生的异常，这些异常可能是由于数据库错误或操作系统问题。

12c之前

FILE="CASE.csv"
export dest_loc=/dest/loc
export LOGFILE=$LOG/100.$NOW_TS.log
echo "$0 started at $(date +"%Y-%m-%d:%H:%M:%S")" > ${LOGFILE}
sqlplus -s $DB_LOGIN << EOSQL >> ${LOGFILE}
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
SET echo OFF
SET feedback OFF    
SET sqlprompt ''    
SET TERMOUT OFF
SET UNDERLINE OFF
SET trimspool ON
SET trimout ON
SET SQLBLANKLINES ON
SET PAGESIZE 50000
SET COLSEP "|"
SET LINESIZE 650
SPOOL $dest_loc/$FILE
@$SRC/sql_to_run.sql
SPOOL OFF
EOSQL
rc=$?
if [ ${rc} -ne 0 ];
then
echo "nThere has been an ORACLE error. RC = $RC"   >> ${LOGFILE}
echo "n$0 ended at $(date +"%Y-%m-%d:%H:%M:%S")"   >> ${LOGFILE}
echo "####################################"  >> ${LOGFILE}
exit 2
else 
echo "nNo ORACLE errors found."   >> ${LOGFILE}
fi

12c及以上

如果你在这个版本中，你可能想使用set markup csv功能来生成文件。

FILE="CASE.csv"
export dest_loc=/dest/loc
export LOGFILE=$LOG/100.$NOW_TS.log
echo "$0 started at $(date +"%Y-%m-%d:%H:%M:%S")" > ${LOGFILE}
sqlplus -s $DB_LOGIN << EOSQL >> ${LOGFILE}
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
SET echo OFF
SET feedback OFF    
SET SQLBLANKLINES ON
SET PAGESIZE 50000
SET LINESIZE 650
SET TERMOUT OFF
SET MARKUP CSV ON DELIMITER "|"
SPOOL $dest_loc/$FILE
@$SRC/sql_to_run.sql
SPOOL OFF
EOSQL
rc=$?
if [ ${rc} -ne 0 ];
then
echo "nThere has been an ORACLE error. RC = $RC"   >> ${LOGFILE}
echo "n$0 ended at $(date +"%Y-%m-%d:%H:%M:%S")"   >> ${LOGFILE}
echo "##########################################"   >> ${LOGFILE}
exit 2;
else 
echo "nNo ORACLE errors found."   >> ${LOGFILE}
fi

Test Sqlplus Exit Status

$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 21 08:32:27 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> create table t1 ( c1 number );
create table t1 ( c1 number )
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
[ftpcpl@scglvdoracd0006 ~]$ echo $?
0
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 21 08:32:43 2021
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> whenever sqlerror exit failure;
SQL> create table t1 ( c1 number );
create table t1 ( c1 number )
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
$ echo $?
1