Выполнение скриптов oracle sql через powershell

Собственно для выполнения внешних sql скриптов нужно:

1. Загрузить библиотеку Oracle.ManagedDataAccess.dll
2. Создать переменную с типом New-Object Oracle.ManagedDataAccess.Client.OracleConnection
3. Создать файл SQL скрипта, который будем использовать в Poweshell.
4. Убрать символы однострочных комментариев (—) и все выводы в консоль: (DBMS_OUTPUT.PUT_LINE)
5. Если использовать скрипт Powershell в TFS, то переделать форматирование или вообще его убрать:
   «Connected to database: {0} running on host: {1} – Servicename: {2} – Serverversion: {3}» -f $con.DatabaseName, $con.HostName, $con.ServiceName, $con.ServerVersion
   TFS такое форматирование не понимает!

ORACLE SQL Скрипт:

declare
    TYPE v_array_type IS VARRAY (100) OF NUMBER;
 
    srv_id_array v_array_type; 
    i INT;   
    check_var INT;  
    
    t2_srv_name server.server_name%TYPE;
    dev_srv_name server.server_name%TYPE;
    
BEGIN
    SELECT DISTINCT s1.SERVER_ID BULK COLLECT
    INTO srv_id_array 
    FROM server_testlab s1, endpoint_testlab e1
    WHERE s1.SERVER_ID = e1.SERVER_ID 
    AND s1.CLOSED_BY IS NULL
    ORDER BY s1.SERVER_ID;
 
 i := 0;
 WHILE i < srv_id_array.count
 LOOP
    i:=i+1;
    check_var := 0;
        
    SELECT COUNT(1)
    INTO check_var
    FROM table_name_testlab
    WHERE prod_server_id = srv_id_array(i); 
    
    IF check_var > 0 THEN
        SELECT t2_server_name 
        INTO t2_srv_name
        FROM table_name_testlab
        WHERE prod_server_id = srv_id_array(i);
                
        SELECT dev_server_name 
        INTO dev_srv_name
        FROM table_name_testlab
        WHERE prod_server_id = srv_id_array(i);
        
        UPDATE ENDPOINT_TESTLAB
        SET address = REPLACE(address,t2_srv_name,dev_srv_name)
        WHERE server_id = srv_id_array(i);
    
    END IF;
 END LOOP; 
END;

Powershell:

param (
   # [string] $SqlServer, 
    [string] $SqlCmdPath
)

Add-Type -Path "C:\Oracle\product\12.2.0\client_1\odp.net\managed\common\Oracle.ManagedDataAccess.dll"


$SqlCmdPath = "C:\ORACLE\sql_script.sql"

try {
	
   write-Output $SqlCmdPath
   
   $sqlQuery = Get-Content -Path $SqlCmdPath -Encoding UTF8

   Write-Output $sqlQuery
   
   $con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("User Id=user;Password=password;Data Source=server/db")

   $con.open()

   Write-Output $con.State

   #"Connected to database: {0} running on host: {1} – Servicename: {2} – Serverversion: {3}" -f $con.DatabaseName, $con.HostName, $con.ServiceName, $con.ServerVersion
   $DN = $con.DatabaseName
   $HN = $con.HostName
   $SN = $con.ServiceName
   $SV = $con.ServerVersion
   Write-Output "Connected to database: $DN"
   write-Output	"Running on host: $HN"
   write-Output "Servicename: $SN" 
   write-Output "Serverversion: $SV"

    $SqlCmd = $con.CreateCommand()
    #$SqlCmd.CommandText = "Select address from Endpoint"
 
    # $rdr = $SqlCmd.ExecuteReader()
    #if ($rdr.Read()) {$rdr.GetString(0)}
	 #while ($rdr.Read())
    #{
    #   $rdr.GetString(0) 
    #}
    #write-output $SqlCmd

    $SqlCmd.CommandText = "$sqlQuery"
    $SqlCmd.ExecuteNonQuery()
    
}

catch

{
    $CS = $con.ConnectionString
    $Error1 = $_.Exception.ToString()
    Write-Error ("Can not open connection: $CS")
	Write-Error ($Error1)
}

finally

{

    if ($con.State -eq "Open") { $con.close() }

}