sql - 如何在while循环内从shell脚本运行插入语句到oracle DB

Question

我有一个用例，在我的 shell 脚本中我有一个 do while 循环，我需要在这个循环期间将记录插入到 oracle 数据库中。所以我期待3个循环，每次插入都应该发生。但是目前我的代码正在进入第一个循环，然后插入一条记录并退出该循环。

#!/bin/sh

ODATE=${1}

file_deliver_time="20210218:12:56:76"
reference_file=/var/tmp/Sneha/SSM_KPI_source_file_checklist.txt
HOME_DIR="/var/tmp/Sneha"

source ~/env/INFORMATICA.env
source ${HOME_DIR}/db.properties

runScript()
{
sql ${USER}/\"${PASS}\"@${DB_INSTANCE} @temp.sql $1 $2 $3 $4

if [[ $? -ne 0 ]];
then
echo "Error";
exit 2;
fi
}

cd ${HOME_DIR}

while read -r line
do
  echo $line
  if [[ -f ${HOME_DIR}/temp.sql ]]; then rm -f ${HOME_DIR}/temp.sql; 
fi
  echo "whenever sqlerror exit failure;" > temp.sql
  echo "set define on echo on"         >> temp.sql
  echo "INSERT INTO test_ssm_kpi 
 (source_system,file_name,delivery_timestamp,ODATE) VALUES ('&1' , 
'&2' , '&3' , '&4');" >> temp.sql
 echo "exit;" >> temp.sql
 file_path=$(echo $line | awk -F ';' '{print $1}' | xargs echo -n )
 file_str=$(echo $line | awk -F ';' '{print $2}' | xargs echo -n )
 file_type=$(echo $line | awk -F ';' '{print $3}' | xargs echo -n )
 file_source_sys=$(echo $line | awk -F ';' '{print $4}' | xargs echo 
 -n )
  echo "runScript ${file_path} ${file_str} ${file_type} 
  ${file_source_sys}"
  runScript ${file_source_sys} ${file_str} ${file_type} ${ODATE}
done < $reference_file

我现在得到的输出如下：

/var/tmp/Sneha;RTPM_POSITIONS_REPORT_;csv;ARTS
runScript /var/tmp/Sneha RTPM_POSITIONS_REPORT_ csv ARTS

SQLcl: Release 18.3 Production on Fri Nov 12 12:01:33 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Fri Nov 12 2021 12:01:34 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL> INSERT INTO test_ssm_kpi 
(source_system,file_name,delivery_timestamp,ODATE) VALUES ('&1' , '&2' , 
'&3' , '&4');
old:INSERT INTO test_ssm_kpi 
(source_system,file_name,delivery_timestamp,ODATE) VALUES ('&1' , '&2' , 
'&3' , '&4')
new:INSERT INTO test_ssm_kpi 
(source_system,file_name,delivery_timestamp,ODATE) VALUES ('ARTS' , 
'RTPM_POSITIONS_REPORT_' , 'csv' , '20211005')

1 row inserted.

SQL> exit;

Disconnected from Oracle Database 19c Enterprise Edition Release 
19.0.0.0.0 - Production
Version 19.11.0.0.0

Note: source_file_checklist.txt this file contains 3 lines of data.

   /var/tmp/Sneha;POSITIONS_REPORT_;csv;ARTS
   /var/tmp/Sneha;SHORTPOSITION_;xml;SNK
   /var/tmp/Sneha;NETPOSITION__;xml;SNT

如何让我的代码循环 3 次，并在每个循环中插入一条记录。我不明白我的 shell 脚本中缺少什么。

非常感谢您提前提供的帮助。

标签： sqloracleshelldo-while

Answer 1

另一种更易于阅读和维护的替代方案。在我的情况下，这只是一个示例，但只需替换您的情况下的插入选择和属性文件配置。

$ pwd
/home/ftpcpl
$ cat reference.txt
/home/ftpcpl;POSITIONS_REPORT_;csv;ARTS
/home/ftpcpl;SHORTPOSITION_;xml;SNK
/home/ftpcpl;NETPOSITION__;xml;SNT

现在，我的测试脚本

#!/bin/sh

ODATE=${1}

file_deliver_time="20210218:12:56:76"
reference_file=/home/ftpcpl/reference.txt
HOME_DIR="/home/ftpcpl"

runScript()
{
${ORACLE_HOME}/bin/sqlplus "/ as sysdba" @temp.sql $1 $2 $3 $4
if [[ $? -ne 0 ]];
then
  exit 2;
fi

}

cd ${HOME_DIR}

while read -r line
do
  echo $line
  if [[ -f ${HOME_DIR}/temp.sql ]]; then rm -f ${HOME_DIR}/temp.sql; fi
  echo "whenever sqlerror exit failure;" > temp.sql
  echo "set define on echo on"         >> temp.sql
  echo "select '&1' , '&2' , '&3' , '&4' from dual;" >> temp.sql
  echo "exit;" >> temp.sql
  file_path=$(echo $line | awk -F ';' '{print $1}' | xargs echo -n )
  file_str=$(echo $line | awk -F ';' '{print $2}' | xargs echo -n )
  file_type=$(echo $line | awk -F ';' '{print $3}' | xargs echo -n )
  file_source_sys=$(echo $line | awk -F ';' '{print $4}' | xargs echo -n )
  echo "runScript ${file_path} ${file_str} ${file_type} ${file_source_sys}"
  runScript ${file_path} ${file_str} ${file_type} ${file_source_sys}
done < /home/ftpcpl/reference.txt

现在，让我们运行它

./test_loop.sh
/home/ftpcpl;POSITIONS_REPORT_;csv;ARTS
runScript /home/ftpcpl POSITIONS_REPORT_ csv ARTS

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 9 17:17:52 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> select '&1' , '&2' , '&3' , '&4' from dual;
old   1: select '&1' , '&2' , '&3' , '&4' from dual
new   1: select '/home/ftpcpl' , 'POSITIONS_REPORT_' , 'csv' , 'ARTS' from dual

'/HOME/FTPCP 'POSITIONS_REPORT 'CS 'ART
------------ ----------------- --- ----
/home/ftpcpl POSITIONS_REPORT_ csv ARTS

SQL> exit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
/home/ftpcpl;SHORTPOSITION_;xml;SNK
runScript /home/ftpcpl SHORTPOSITION_ xml SNK

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 9 17:17:54 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> select '&1' , '&2' , '&3' , '&4' from dual;
old   1: select '&1' , '&2' , '&3' , '&4' from dual
new   1: select '/home/ftpcpl' , 'SHORTPOSITION_' , 'xml' , 'SNK' from dual

'/HOME/FTPCP 'SHORTPOSITION 'XM 'SN
------------ -------------- --- ---
/home/ftpcpl SHORTPOSITION_ xml SNK

SQL> exit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
/home/ftpcpl;NETPOSITION__;xml;SNT
runScript /home/ftpcpl NETPOSITION__ xml SNT

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 9 17:17:55 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> select '&1' , '&2' , '&3' , '&4' from dual;
old   1: select '&1' , '&2' , '&3' , '&4' from dual
new   1: select '/home/ftpcpl' , 'NETPOSITION__' , 'xml' , 'SNT' from dual

'/HOME/FTPCP 'NETPOSITION_ 'XM 'SN
------------ ------------- --- ---
/home/ftpcpl NETPOSITION__ xml SNT

SQL> exit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0