如何验证sqlplus是否可以连接? [英] How to verify sqlplus can connect?

问题描述

我想知道是否有可能以某种方式获取sqlplus输出以发现我的数据库是否正常.

I'd like to know if its possible to get sqlplus output in some way to discover if my database is up.

我想在数据库上运行脚本列表，但在此之前，我想知道数据库是否已启动并使用我的脚本运行.

I want to run a list of scripts on a database, but before I do that, I want to know if the database is up and running with my script.

这是我尝试过的:

 sqlplus /@DB1 << EOF
 > select 1 from dual;
 > EOF

它无法连接，但是sqlplus的返回代码仍然显示一切正常"！

It cannot connect, but the return code of sqlplus still says "everything OK"!

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 28 10:06:41 2016

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

ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor


Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where  ::= [/][@]
       ::= [][/][@]
Enter user-name: ju@srv:/tmp/jcho $ echo $?
0

我知道我可以grep测试查询的结果，像这样:

I know I could grep the result of my test query, like that:

sqlplus /@DB1 << EOF
  select 'ALL_GOOD_BOY' from dual;
EOF

致电:

如果连接有效，则给出1行，否则，0:

$ a.sh |grep ALL_GOOD_BOY|wc -l

...这对我来说似乎有很多步骤.在无法连接"给出错误"返回码的模式下设置sqlplus的其他方法?

... This seems many steps to me. Any other way to set sqlplus in a mode where "unable to connect" gives an "error" return code?

推荐答案

感谢@Kacper提供的参考，我可以根据自己的情况来修改 sqlplus /nolog ;这是主意:

Thanks to the reference given by @Kacper, I could adapt this sqlplus /nolog to my case; here's the idea:

1. 仅打开 sqlplus 而不连接
2. 在SQLERROR上设置特定的返回码-这是connect失败时发生的情况
3. 返回代码可以像往常一样在调用者脚本中收集:

1. open sqlplus only without connecting
2. set a specific return code on SQLERROR - this is what happens when connect fails
3. return code can be collected as usual in the caller script:

a.sh

sqlplus /nolog << EOF
 WHENEVER SQLERROR EXIT 50
 WHENEVER OSERROR EXIT 66
 connect /@${MISTERY_DB}
 exit;
EOF

然后拨打电话:

/ju $ export MISTERY_DB="eg_NON_EXISTING_DB"
/ju $ a.sh
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 29 08:43:44 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL> SQL> SQL> ERROR:
  ORA-12154: TNS:could not resolve the connect identifier specified
/ju $ echo $?
50

与此相关:在以下位置连接到sqlplus一个shell脚本并运行SQL脚本

这篇关于如何验证sqlplus是否可以连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！