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

$files = @(gci | where {!$_.PsIsContainer})
$count = 0
foreach ($file in $files) {
write-progress -activity "Running scripts for $user" `
-currentoperation $file.name -status Executing `
-PercentComplete (100*$count++/$files.count)
Invoke-Script $server $user $password $file
New-Directory ".\Completed"
move-item -path $file.fullname -destination ".\Completed"
-Force
}
write-progress -activity "Running scripts for $user" `
-status Complete -completed
popd
"Completed running $count script(s) for $user on $server"
}
4. Deine a function to run a given SQL ile using SQL*Plus :
function Invoke-Script($server, $user, $password, $file) {
$logDir = ("..\Logs\{0}\{1}" -f $script:_dateId, $user)
New-Directory $logDir
$logFile = join-path $logDir ($file.basename + ".html")
"Running $file against $user@$server"
(Get-SqlPlusSQL $file.fullname) | sqlplus.exe -L -M "HTML ON
SPOOL ON" `
-S "$user/""$password""@$server" >> $logfile 2>$1
"Ran $file against $user@$server. Details at $logfile"
$script:_runCount++
if ($LASTEXITCODE -ne 0) {
write-error ("ERROR executing {0}!" -f $file.FullName)
invoke-item $logFile; exit
}
}
5. Deine a function that will read the contents of a SQL ile and surround it with
SQL*Plus commands to get the desired output:
function Get-SqlPlusSQL($filename) {
@"
whenever sqlerror exit sql.sqlcode
set echo off
set termout off