抑制批处理脚本中的SQL * PLUS错误 [英] Suppress SQL*PLUS error in batch script

问题描述

我有一个脚本 db.bat ，如下所示:

I have a script db.bat as below:

@echo off

setlocal enabledelayedexpansion
 for /F "tokens=*" %%A in (user.txt) do (
    sqlplus -s %%A @fetch.sql  >> output.txt 

 )

其中 user.txt (我需要其有效期的所有用户详细信息列表.此列表可能包含40至50行):

where user.txt (list of all user details for which I need expiry date. this list may have around 40-50 rows) is:

dbuser/password@database1
readuser/p@ssw0rd@database1
adminuser/Pa$$word@database2
.......
.......
.......

和 fetch.sql 是:

set pagesize 20
set linesize 200

select username, expiry_date from user_users;
exit;

我在这里面临的问题是，每当我的脚本db.bat遇到如下所示的任何SQL错误时，它就不会进一步移动并在这一点上被挂起，直到我手动将其停止为止.

The problem I am facing here is, whenevey my script db.bat encounters any SQL ERRORS like given below, its not moving further and getting hanged at that point until I manually stop that.

SQL错误:

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


ERROR:
ORA-28000: the account is locked 

我已经检查了在这种情况下可以使用的WHENEVER SQLERROR命令，但是不知道如何在这里使用它.

I have checked that there is a WHENEVER SQLERROR command that works in this situation but don't know how I can use it here.

推荐答案

对于这些类型的错误，SQL * Plus正在挂起"用户名提示符，因为它无法连接.由于-s标志，您看不到它.默认情况下，它将允许进行三次尝试，这在交互式运行时很有用，而在从此类脚本运行时则无济于事.您可以使用 登录"选项:

For those kinds of errors, SQL*Plus is 'hanging' at a username prompt, as it hasn't been able to connect. You don't see that because of the -s flag. By default it will allow three attempts, which is useful when running interactively, but isn't helpful when run from a script like this. You can make it exit after the failed login with the -l 'logon' option:

sqlplus -s -l %%A @fetch.sql  >> output.txt

这篇关于抑制批处理脚本中的SQL * PLUS错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！