Instant Oracle Database and PowerShell How-to - Instant Oracle Database and PowerShell How-to - page 50

How to do it...

1. Create SqlPlus.ps1 and add the main driver script that will iterate through

subfolders of the script's directory and execute all SQL iles it inds. Each subfolder
will be named to match an Oracle user/schema:
function Main {
$server = read-host "Server TNS name"
pushd (Get-ScriptDirectory)
$userDirs = @(gci | ? {$_.PsIsContainer -and $_.Name -ne
"Logs"})
foreach ($dir in $userDirs) {
$user = $dir.Name
$password = Get-Password($user)
$sw = [System.Diagnostics.StopWatch]::StartNew()
$runCountBefore = $script:_runCount
Invoke-Scripts $server $user $password
$sw.Stop()
"Ran $($script:_runCount - $runCountBefore) script(s) " +
"for $user@$server in $($sw.Elapsed.TotalSeconds)
second(s)"
}
popd
"Finished. Ran $script:_runCount script(s) total"
}

$script:_dateId = "{0:MM-dd-yyyy.hh-mm-ss}" -f (Get-Date)
$script:_runCount = 0
Main

2. Deine a utility function to prompt for, and return a password:
function Get-Password($user) {
$pwd = read-host -AsSecureString "Password for $user@$server"
[System.Runtime.InteropServices.Marshal]::PtrToStringAuto(`
[System.Runtime.InteropServices.Marshal]::SecureStrin
gToBSTR($pwd))
}

3. Deine a function to iterate through the iles in the subfolder matching the user/
schema, execute each, and move executed iles to a completed folder:
function Invoke-Scripts($server, $user, $password) {
"Running scripts for $user on $server"
pushd $user