Post reference: https://powershell.org/forums/topic/run-multiple-sql-scripts-in-powershell/

powershell-forum-run-multiple-sql-scripts-in-powershell-reply-01.ps1

$crlf = [System.Environment]::NewLine
[string] $TNS = 'LINKED_SERVER'
$sqlfiles = "c:\temp\script1.sql", "c:\temp\script2.sql"
$connection=New-Object DATA.OracleClient.OracleConnection("Data Source=$TNS;User Id=TEST;Password=XXXXXXXX")

    foreach ($sqlfile in $sqlfiles){

    $FileLines = Get-Content $sqlfile
    $query = [string]::Join($crlf,$FileLines)
    #log start time
    [datetime]$Now = Get-Date
    [string] $TSbegin = $Now.ToString("yyyy-MM-dd HH:mm:ss")
    echo "`n`nExecuting script $sqlfiles Started at: $TSbegin"
    [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
    
    $connection.Open()
    $res = (new-Object DATA.OracleClient.OracleCommand($query,$connection))
	$output = $res.ExecuteScalar()
     
    #log end time
    [string]$TSend = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $Duration = New-TimeSpan -Start ($TSbegin) -End (Get-Date)
    echo "`n$('{0:N0}' -f $output) Rows returned"
    echo "`nExecuting script $sqlfiles completed at: $TSend"
    $Duration |Format-Table -AutoSize 
    # Remove-Variable res
   }

$connection.Close()

Hi, I want to run Oracle Sql Script using powershell. When I use your script I see. what needs to be changed. How do I change this to 64bit ?

Exception calling "Open" with "0" argument(s): "Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit
Oracle client components installed."
At D:\Jams\script\try\onemore.ps1:16 char:5

• $connection.Open()
  

• ~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  • FullyQualifiedErrorId : InvalidOperationException

Exception calling "ExecuteScalar" with "0" argument(s): "Invalid operation. The connection is closed."
At D:\Jams\script\try\onemore.ps1:18 char:2

• $output = $res.ExecuteScalar()
  

• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  • FullyQualifiedErrorId : InvalidOperationException