bash 将 oracle sql 查询的输出分配给 shell 脚本中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48426207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Assign output of an oracle sql query to a variable in shell script
提问by Sai Avinash
im trying to save the output of a query in a variable. I followed some answers here in stackoverflow but no luck. Currently i'm using this
我试图将查询的输出保存在一个变量中。我在stackoverflow中遵循了一些答案，但没有运气。目前我正在使用这个
billerrors=$(sqlplus -s $username/$password@$SID << EOF
set pagesize 0 feedback off verify off heading off echo off;
SELECT ERROR from temp_table
exit;
EOF
)
echo $billerrors
The output i always get is
我总是得到的输出是
SQLPlus: Release 11.2.0.1.0 Production Copyright (c) 1982, 2009, Oracle. All rights reserved. Use SQLPlus to execute SQL, PL/SQL and SQL*Plus statements.
SQL Plus：11.2.0.1.0 版生产 版权所有 (c) 1982、2009，Oracle。版权所有。使用 SQLPlus 执行 SQL、PL/SQL 和 SQL*Plus 语句。
The output of the above query SELECT ERROR from temp_table will be in the below format
上述查询 SELECT ERROR from temp_table 的输出将采用以下格式
Bill payment errors in the last 30 minutes: XX : 70. YY : 20.
Incase of no bill errors, the output will be
万一没有账单错误，输出将是
Bill payment errors in the last 30 minutes: 0.
I would really appreciate if someone could help
如果有人可以提供帮助，我将不胜感激
回答by Dmitry Demin
You use the wrong names in the script of the variable username and password. If you use the correct names in the script for the variables username and password, then your script must work.
您在变量用户名和密码的脚本中使用了错误的名称。如果您在脚本中为变量 username 和 password 使用了正确的名称，那么您的脚本必须工作。
Exapmple 1. Correct names in the script for the variables $usernameand $password.
示例 1。脚本中变量$username和 的正确名称$password。
#!/bin/sh
username=SCOTT
password=Tiger
## esmd is TNS alias in tnsnames.ora
SID=esmd
echo username: $username
echo password: $password
echo tns: $SID
billerrors=$(sqlplus -s $username/$password@$SID << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)
echo $billerrors
oracle@esmd:~> ./test.sh
username: SCOTT
password: Tiger
tns: esmd
USER is "SCOTT" Date: 25-01-2018 08:32 The test is passed
Example 2.I make an error in the name of the variable $UserName, when I call sqlplus
示例 2.$UserName当我调用 sqlplus 时，我
在变量名中出错
#!/bin/sh
username=SCOTT
password=Tiger
## esmd is TNS alias in tnsnames.ora
SID=esmd
echo username: $username
echo password: $password
echo UserName: $UserName
echo tns: $SID
billerrors=$(sqlplus -s $UserName/$password@$SID << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)
echo $billerrors
oracle@esmd:~> ./test.sh
username: SCOTT
password: Tiger
UserName:
tns: esmd
SQL*Plus: Release 11.2.0.3.0 Production Copyright (c) 1982, 2011, Oracle. All rights reserved. Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus
statements. Usage 1: sqlplus -H | -V -H Displays the SQL*Plus version and the
usage help. -V Displays the SQL*Plus version. Usage 2: sqlplus [ [<option>] [{logon | /nolog}] [<start>] ] <option> is: [-C <version>] [-L] [-M "<options>"]
[-R <level>] [-S] -C <version> Sets the compatibility of affected commands to the version specified by <version>. The version has the form "x.y[.z]".
For example, -C 10.2.0 -L Attempts to log on just once, instead of reprompting
on error. -M "<options>" Sets automatic HTML markup of output. The options have the form: HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}]
[SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}] -R <level> Sets restricted mode to disable SQL*Plus commands that interact with the file system. The level can be
1, 2 or 3. The most restrictive is -R 3 which disables all user commands
interacting with the file system. -S Sets silent mode which suppresses the
display of the SQL*Plus banner, prompts, and echoing of commands. <logon> is:
{<username>[/<password>][@<connect_identifier>] | / } [AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value] Specifies the database account username, password and connect identifier for the database connection. Without a connect identifier, SQL*Plus connects to the default database. The AS SYSDBA, AS SYSOPER and AS SYSASM options are database administration privileges. <connect_identifier> can be in the form of Net Service Name or Easy Connect. @[<net_service_name> | [//]Host[:Port]/<service_name>] <net_service_name> is a simple name for a service that resolves to a connect descriptor. Example: Connect to database using Net Service Name and the database net service name is ORCL. sqlplus myusername/mypassword@ORCL Host specifies the host name or IP address of the database server computer. Port specifies the listening port on the database server. <service_name> specifies the service name of the database you want to access. Example: Connect to database using Easy Connect and the Service name is ORCL. sqlplus myusername/mypassword@Host/ORCL The /NOLOG option starts SQL*Plus without connecting to a database. The EDITION specifies the value for Session Edition. <start> is: @<URL>|<filename>[.<ext>] [<parameter> ...] Runs the specified SQL*Plus script from a web server (URL) or the local file system (filename.ext) with specified parameters that will be assigned to substitution
variables in the script. When SQL*Plus starts, and after CONNECT commands, the
site profile (e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
(e.g. login.sql in the working directory) are run. The files may contain
SQL*Plus commands. Refer to the SQL*Plus User's Guide and Reference for more
information.
Example 3.I make an error in the name of the variable $Password, when I call sqlplus
示例 3.$Password当我调用 sqlplus 时，我
在变量名中出错
#!/bin/sh
username=SCOTT
password=Tiger
## esmd is TNS alias in tnsnames.ora
SID=esmd
echo username: $username
echo password: $password
echo UserName: $UserName
echo Password: $Password
echo tns: $SID
billerrors=$(sqlplus -s $username/$Password@$SID << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)
echo $billerrors
oracle@esmd:~> ./test.sh
username: SCOTT
password: Tiger
UserName:
Password:
tns: esmd
SQL*Plus: Release 11.2.0.3.0 Production Copyright (c) 1982, 2011,
Oracle. All rights reserved. Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus
statements. Usage 1: sqlplus -H | -V -H Displays the SQL*Plus version and the
Example 4I make an error in the name of the variable $Sid, when I call sqlplus
示例 4$Sid当我调用 sqlplus 时，我
在变量名中出错
#!/bin/sh
username=SCOTT
password=Tiger
## esmd is TNS alias in tnsnames.ora
SID=esmd
echo username: $username
echo password: $password
echo SID: $SID
echo Sid: $Sid
billerrors=$(sqlplus -s $username/$password@$Sid << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)
echo $billerrors
oracle@esmd:~> ./test.sh
username: SCOTT
password: Tiger
SID: esmd
Sid:
SQL*Plus: Release 11.2.0.3.0 Production Copyright (c) 1982, 2011, Oracle. All rights reserved. Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus
Example 5I make an error in the value of the variable $password=tiger, when I call the sqlplus program
示例 5我在$password=tiger调用 sqlplus 程序时变量的值出错
#!/bin/sh
username=SCOTT
## True password is Tiger
password=tiger
## esmd is TNS alias in tnsnames.ora
SID=esmd
echo username: $username
echo password: $password
echo SID: $SID
billerrors=$(sqlplus -s $username/$password@$SID << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)
echo $billerrors
oracle@esmd:~> ./test.sh
username: SCOTT
password: tiger
SID: esmd
ERROR: ORA-01017: invalid username/password; logon denied SP2-0306: Invalid
option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}]
[edition=value]] where <logon> ::= <username>[/<password>]
[@<connect_identifier>] <proxy> ::= <proxyuser>[<username>][/<password>]
[@<connect_identifier>] SP2-0306: Invalid option. Usage: CONN[ECT]
ORACLE after 3 attempts, exiting SQL*Plus
Example 6I make an error in the value of the variable $SID=esm.
示例 6我在变量的值中出错$SID=esm。
#!/bin/sh
username=SCOTT
password=tiger
## esmd is TNS alias in tnsnames.ora
## True is esmd
SID=esm
echo username: $username
echo password: $password
echo SID: $SID
billerrors=$(sqlplus -s $username/$password@$SID << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)
echo $billerrors
oracle@esmd:~> ./test.sh
username: SCOTT
password: tiger
SID: esm
ERROR: ORA-12154: TNS:could not resolve the connect identifier specified
SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS
YSDBA|SYSOPER|SYSASM}] [edition=value]] where <logon> ::= <username>[<username>]
[/<password>][@<connect_identifi>] SP2-0157: unable to CONNECT to ORACLE after 3
attempts, exiting SQL*Plus
Example 7If the Username is case sensitive.
示例 7如果用户名区分大小写。
#!/bin/sh
username=\"Scott\"
password=TigeR
## esmd is TNS alias in tnsnames.ora
SID=esmd
echo username: $username
echo password: $password
echo SID: $SID
billerrors=$(sqlplus -s $username/$password@$SID << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user;
SELECT 'Date: '||to_char(sysdate,'DD-MM-YYYY HH24:MI')||' The test is passed' from dual;
exit;
EOF
)
echo $billerrors
oracle@esmd:~> ./test.sh
username: "Scott"
password: TigeR
SID: esmd
USER is "Scott" Date: 25-01-2018 09:20 The test is passed
oracle@esmd:~> ./test.sh
username: "Scott"
password: TigeR
SID: esmd
USER is "Scott" Date: 25-01-2018 09:23 The test is passed
