CODEWITHSUNDEEP
oracle-databasepowershellsqlplus
user1205746
user1205746

Reputation: 3364

Make SQL*Plus script quit

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.

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?

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
}

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.

Is it possible to do that?

Upvotes: 1

Views: 3918

Answers (2)

William Robertson
William Robertson

Reputation: 16001

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

Output:

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>

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>

Tested with SQL*Plus 12.2 on Windows 10.

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

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

Upvotes: 2

Jason Boyd
Jason Boyd

Reputation: 7029

I'm not familiar plsql but I gather that sqlplus is an exe that executes plsql scripts. If PowerShell is not continuing to the next script it is because sqlplus is not exiting. A little googling suggests that the proper way to end a pssql script is to put a single forward slash on the last line:

/

From what I understand this tells sqlplus to execute everything above, without it sqlplus will not terminate.

You say:

it is not feasible to add quit command in each script

But as far as I can tell that is exactly what you have to do.

Luckily PowerShell can help with that too. Just navigate to the directory containing the scripts and execute the following:

dir *.sql | add-content -value "/"

Upvotes: 1

Related Questions