仅连接一次sqlplus而无需循环写入文件 [英] connect to sqlplus only once without writing to a file in a loop

问题描述

我有一个要求，我需要编写一个ksh脚本，该脚本将命令行参数读入数组并创建DML语句以将记录插入到oracle数据库中.我创建了如下脚本来实现这一目标.但是，调用脚本的用户无权写入必须运行脚本的目录.因此，有没有一种方法可以在数据库中触发多个插入而无需在循环内多次连接到sqlplus，并且同时不按以下方式创建临时sql文件?任何想法都受到高度赞赏.预先感谢！

I have a requirement for which I need to write a ksh script that reads command line parameters into arrays and creates DML statements to insert records into an oracle database. I've created a script as below to achieve this. However, the user invoking the script doesn't have permission to write into the directory where the script has to run. So, is there a way we can fire multiple inserts on the database without connecting to sqlplus multiple times within the loop and at the same time, NOT create temp sql file as below? Any ideas are highly appreciated. Thanks in advance!

i=0
while (( i<$src_tbl_cnt ))
do
echo "insert into temp_table values ('${src_tbl_arr[$i]}', ${ins_row_arr[$i]},  ${rej_row_arr[$i]});" >> temp_scrpt.sql
(( i+=1 ))
done
echo "commit; disc; quit" >> temp_scrpt.sql
sqlplus user/pass@db @ temp_scrpt.sql

推荐答案

您可以直接在外壳程序脚本中通过管道将输入生成块的输出传递到sqlplus中，而不必创建临时文件.

Instead of creating a temporary file you can directly pipe the output of an input generating block into sqlplus, in your shell script.

示例:

{
  echo 'set auto off;'
  for ((i=0; i<100; i++)); do
    echo "insert into itest(i) values ($i);"
  done
  # echo 'rollback;' # for testing
  echo 'commit;'
} | sqlplus -S juser/secret@db > /dev/null

这适用于Ksh 93和Bash(可能甚至使用Ksh 88以((表达式语法为模).

This works with Ksh 93 and Bash (perhaps even with Ksh 88 modulo the (( expression syntax).

测试表的相应DDL语句:

The corresponding DDL statement for the test table:

create table itest ( i number(36) ) ;

PS:顺便说一句，即使首选创建临时文件-重定向输出比对每行执行附加样式重定向的方式更为有效，例如:

PS: Btw, even when creating a temporary file is preferred - redirecting the output is way more efficient than doing an append-style redirect for each line, e.g.:

{ for ((i=0; i<100; i++)); do echo "line $i"; done; echo end; } > foo.tmp

这篇关于仅连接一次sqlplus而无需循环写入文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！