Database Reference
In-Depth Information
How it works...
In the
Main
script we start by prompting for a server TNS name entry with
Read-Host
and
changing to the script directory. Next,
Get-ChildItem
(
gci
) is invoked to get the items in
the script directory that are folders (
$_.PsIsContainer
) not named
Logs
. For each directory,
the directory name is used for user/schema name, a password is read from the host, and these
values are passed to
Invoke-Scripts
to run the SQL iles in that subdirectory.
Invoke-Scripts
iterates through the iles in the folder and calls
Invoke-Script
to
execute each. It also takes care of progress reporting and moving executed scripts to a
Completed
subdirectory of the user/schema folder. This is more important if the SQL iles
have table schema changes and data migrations that may not be intended to be run multiple
times. Note we are not recursively iterating folders and iles here. If script iles needed to be
run in a certain order, we'd want to add a numeric preix to the iles so they were iterated over
and executed in the right sequence.
Invoke-Script
irst sets up a log ilename for the script to be executed and invokes
Get-SqlPlusSQL
to build an adjusted SQL statement for the script before piping that
SQL to SQL*Plus. Since
sqlplus.exe
is in the
ORACLE_HOME
directory and that's in the
PATH
environment variable, we don't need to specify the path to it. The
-L
parameter tells
sqlplus.exe
to only attempt to log on once; otherwise any bad credentials could cause
an input prompt and hang the script. The
-M
parameter indicates we want HTML output.
The
-S
parameter invokes silent mode to prevent echoing of commands, banners, and
prompts. Credentials are passed to
sqlplus.exe
using the user/password@server format.
Output is redirected to
$logfile
and
2>$1
directs standard error to standard output. After
this,
$LASTEXITCODE
is checked; 0 indicates success otherwise it is the Oracle error number.
Any error is written with
Write-Error
, the log ile is launched using
Invoke-Item
, and the
script immediately terminates with
exit
since one script failure could impact other scripts to
be executed.
The
Get-SqlPlsSQL
function uses
whenever sqlerror exit sql.sqlcode
to ensure
$LASTEXITCODE
will be set in the event of an error. It then sets
echo
and
termout
to
off
and includes the SQL contents of
$filename
with
cat
. A
commit
statement is added in
case
$filename
contains insert, update, or delete statements without a commit; SQL*Plus
should auto commit on exit as a default but here we are being explicit. Finally,
exit
is used to
terminate SQL*Plus.
This approach provides a generic way to automate running any number of SQL scripts with
PowerShell and SQL*Plus. In this example we're just reapplying some procedures and views
but the same script could be used to execute hundreds of DML, DDL, or DCL changes that are
common for application deployments.
Search WWH ::
Custom Search