退出 SQL*Plus 脚本 [英] Make SQL*Plus script quit

问题描述

我在一个目录中有一些 SQL*Plus 脚本，我想使用 powershell 自动运行这些脚本.我可以读取目录，但是当我尝试执行脚本时，脚本终止但没有退出 powershell 并转到下一个.

I have a few SQL*Plus scripts in a directory that I would like to run automatically using powershell. I can read the directory and but when I tried to execute the script, the scripts terminated but did not quit powershell and go to the next one.

如何让 powershell 退出 SQL*Plus 并自动转到下一个脚本，而无需通过在每个脚本的末尾显式添加退出来修改脚本?

How do I make powershell quit SQL*Plus and go to the next script automatically without me modifying the scripts by explicitly adding a quit at the end of each script?

以下是最简单形式的说明性 powershell 脚本

Below is the illustrated powershell script in its simplest form

$Filename=Get-childitem "D:\test\TestSQL" -Filter *.sql | Select -ExpandProperty name
foreach ($f in $Filename)
{
  sqlplus -L -S <username>/<password>@<sid> @$f
}

该目录理论上可以包含数百个脚本，在每个脚本中添加退出命令是不可行的.我想在完成每个脚本后强制 SQL*Plus 退出并转到下一个.

The directory can theoretically contain hundreds of scripts and it is not feasible to add quit command in each script. I would like to force SQL*Plus to quit after completing each script and move on to the next one.

可以这样做吗?

推荐答案

您可以将 exit 这个词通过管道传送到 SQL*Plus 命令行.例如，如果 demo.sql 包含以下内容:

You can pipe the word exit into the SQL*Plus command line. For example, if demo.sql consists of this:

prompt This is my demo script

那么你可以这样称呼它:

Then you can call it like this:

echo exit | sqlplus william/w @demo.sql

输出:

Y:\SQL>echo exit | sqlplus william/w @demo.sql

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 13 10:47:13 2019

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

Last Successful login time: Sun Jan 13 2019 10:46:03 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


This is my demo script
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Y:\SQL>

或者使用 -s(静默)选项来抑制横幅等:

Or using the -s (silent) option to suppress banners etc:

Y:\SQL>echo exit | sqlplus -s william/w @demo.sql

This is my demo script

Y:\SQL>

在 Windows 10 上使用 SQL*Plus 12.2 进行测试.

Tested with SQL*Plus 12.2 on Windows 10.

(来自 https://serverfault.com/q/87035/352734 - 事实证明它适用于两个 Windows和 Linux.)

(From https://serverfault.com/q/87035/352734 - turns out it works in both Windows and Linux.)

您还可以查看避免处理用户名和密码的选项，例如在这里:https://dba.stackexchange.com/a/109170/103604

You could also look at options for avoiding handling usernames and passwords e.g. here: https://dba.stackexchange.com/a/109170/103604

这篇关于退出 SQL*Plus 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！