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