需要在 shell 脚本中一次对两个 Oracle DB 执行一个 sql 查询，并将数据导出到单独的 csv 文件 [英] Needs to execute one sql query against two Oracle DBs in shell script at a time and export the data to separate csv files

问题描述

我有 file1.sh 文件，它在内部需要同时对两个 Oracle 数据库执行一个 sql 查询，并且需要将日期导出到 csv 文件，下面是对两个数据库执行查询的示例 shellscript.

I have file1.sh file and which internally needs to execute one sql query against two Oracle DBs at a same time and needs to export date to csv fiiles, below is the sample shellscript which executes the query against two dbs.

    ....
    #!bin/bash
set -X
        sqlplus -S ${user1}@${DBCONNECTIONNAME_1}/${Pwd} Datesquery.sql & >> ${Targetdirectory}/csvfile1.csv
                         sqlplus -S ${user1}@${DBCONNECTIONNAME_2}/${Pwd} Datesquery.sql & >> ${Targetdirectory}/csvfile2.csv
                         sed 1d csvfile2.csv > file2noheader.csv
                         cat csvfile1.csv file2noheader.csv > ${Targetdirectory}/Expod.csv
    ....

但它没有连接到数据库，也没有执行任何查询，只是显示 sqlplus 手册如何使用连接字符串，请让我知道如何针对两个数据库调用一个查询并在 parrallay 中执行它们并将输出绑定到单独的 csv 文件.

But it does not connect to DB and does not execute any query and simply displays sqlplus manual as how to use the connection string, please let me know how to call one query against two dbs and execute them in parrallay and binds output to separate csv files.

推荐答案

一个给定的 sqlplus 会话一次只能连接到一个 db，因此您的同时"要求基本上是不可能的.如果同时"真的意味着顺序地，在同一个脚本中"，那么您又要修复连接字符串了.在那一点上，你比大都会队的早期比赛犯了更多的错误"(向任何纽约大都会队的球迷道歉).

A given sqlplus session can only connect to one db at a time, so your requirement 'at the same time' is essentially a non-starter. If 'at the same time' really means 'sequentially, in the same script, then you are back to fixing your connect string. And at that you 'have more errors than an early Mets game' (with apologies to any NY Mets fans).

首先，您的脚本表明您的 sqlplus 命令是遵循 shell 处理器规范和set -x"的第一个实际命令.然而，您大量使用环境变量作为用户名、密码和连接名称的替代品 - 从未设置这些变量.

First, your script indicates that your sqlplus command is the very first actual command following specification of your shell processor and 'set -x'. Yet you make heavy use of environment variables as substitutions for username, password, and connection name - without ever setting those variables.

其次，您对&"的使用在命令行中对我和解析器来说都是完全混乱的.

Second, your use of an '&' in the command line is totally confusing to both me and the parser.

第三，你需要在引用sql脚本之前加上'@'.

Third, you need to preceed your reference to the sql script with '@'.

第四，你在命令行中的元素顺序都是错误的.

Fourth, your order of elements in the command line is all wrong.

试试这个

#!/bin/bash
orauser1=<supply user name here>
orapw2=<supply password here>
oradb_1=<supply connection name of first database>
#
orauser1=<supply user name here>
orapw2=<supply password here>
oradb_1=<supply connection name of first database>
#
Targetdirectory=<supply value here>
#
sqlplus -S ${orauser1}/${orapw1}@${oradb_1} @Datesquery.sql >> ${Targetdirectory}/csvfile1.csv

sqlplus -S ${orauser2}/${orapw2}@${oradb_1} @Datesquery.sql >> ${Targetdirectory}/csvfile2.csv

这篇关于需要在 shell 脚本中一次对两个 Oracle DB 执行一个 sql 查询，并将数据导出到单独的 csv 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！