在脚本中运行 sql 查询时抑制消息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4381071/
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
suppressing messages while running sql queries in a script
提问by codeObserver
I am trying to write a simple query-script that gets me cnt of rows in a table. However I am facing problem to suppress all sorts of oracle messages. All I am interested is the output:
我正在尝试编写一个简单的查询脚本，它可以让我获得表中的行数。但是，我面临着抑制各种 oracle 消息的问题。我感兴趣的是输出：
Here is my script:
这是我的脚本：
#!/usr/bin/ksh
sqlplus /nolog <<EOF
connect user/pswd@databse
set serveroutput on
set heading off
set feedback off
select count(*) from table;
exit;
EOF
My output looks like this:
我的输出如下所示：
.desktop% sh sql.ksh
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Dec 7 12:00:42 2010
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> Connected.
SQL> SQL> SQL> SQL>
70
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
All I want is the number 70 without any message so that I can write it to logs etc regularly. I know I can parse for the number but I ll have to change that every time my query or schema changes. Can't I just ask mysqlplus to suppress all those messages?
我想要的只是没有任何消息的数字 70，以便我可以定期将其写入日志等。我知道我可以解析这个数字，但是每次我的查询或架构更改时我都必须更改它。我不能只要求 mysqlplus 取消所有这些消息吗？
回答by maxschlepzig
You have to add the uppercase Soption to sqlplus.
您必须将大写S选项添加到sqlplus.
The help message (of sqlplus that comes with Oracle 11.2.0.4.0) specifies:
帮助消息（Oracle 11.2.0.4.0 附带的 sqlplus）指定：
-S Sets silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands.
-S Sets silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands.
With something like
像这样的东西
$ sqlplus -S /nolog << EOF
connect user/pswd@databse
set serveroutput on
set heading off
set feedback off
exec package.procedure(); -- procedure that calls DBMS_OUTPUT procedures ...
select 2 from dual;
-- ...
exit;
EOF
you only get the output from the DBMS_OUTPUT buffer and the results from select statements.
您只能从 DBMS_OUTPUT 缓冲区获得输出以及从 select 语句中获得结果。
回答by josephj1989
You need to use sqlplus -s for silent mode
您需要使用 sqlplus -s 进行静默模式
#!/usr/bin/ksh
sqlplus -s /nolog <<EOF
connect user/pswd@databse
set serveroutput on
set heading off
set feedback off
select count(*) from table;
exit;
EOF
回答by Emil Sit
Try the -sflag. e.g.,
试试-s国旗。例如，
sqlplus /s /nolog <<EOF
...
...
