Собственно для выполнения внешних sql скриптов нужно:
- Загрузить библиотеку Oracle.ManagedDataAccess.dll
- Создать переменную с типом New-Object Oracle.ManagedDataAccess.Client.OracleConnection
- Создать файл SQL скрипта, который будем использовать в Poweshell.
- Убрать символы однострочных комментариев (—) и все выводы в консоль: (DBMS_OUTPUT.PUT_LINE)
- Если использовать скрипт 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 Скрипт:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
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() } } |