PowerShell и Oracle

В этой статье хотел бы поделиться опытом использования PowerShell для выполнения запросов в базе данных Oracle. Вот скрипт, который выполняет подключение к базе данных, выполняет запрос и возвращает результат в виде объекта, где содержится код ошибки, данные, если это запрос select и транзакция, которую надо подтвердить или откатить.

Больше можно узнать, загрузив эту функцию и выполнив Get-Help Send-OracleQuery.

function Send-OracleQuery() {
<# 
.SYNOPSIS 
This function run Oracle query. 
.DESCRIPTION 
This function run Oracle query. 

It uses Oracle.ManagedDataAccess.dll - odp.net library, which is available in ODAC. 
You should place Oracle.ManagedDataAccess.dll to path which is set inside this function. 

This function returns object which contains properties: 
result      - this is a code of result. 0 - success, 1 - fail; 
data        - data which your query returns. 
              It is table for select query and number of rows affected for insert, update and delete queries.       
              If result is 1 data property will contain text of error; 
transaction - transaction which you have to commit or rollback. 
.PARAMETER 
DataBaseName The name of database where query will be executed. 
.PARAMETER DataBaseUser 
Database user name. 
.PARAMETER DataBasePassword 
Database user password. 
.PARAMETER Query 
String of oracle query. 
.EXAMPLE 
$Result = Send-OracleQuery -DataBaseName 'dbname' -DataBaseUser 'username' -DataBasePassword 'P@ssw0rd' -Query "update table set column1='value1' where column1='value2'"; $Result.Transaction.Commit() 
.EXAMPLE 
$Table = Send-OracleQuery -DataBaseName 'dbname' -DataBaseUser 'username' -DataBasePassword 'P@ssw0rd' -Query 'select * from dual'
#>

  param(
    [Parameter(Mandatory=$True)][ValidateNotNullOrEmpty()]
    [string]$DataBaseName,
    [Parameter(Mandatory=$True)][ValidateNotNullOrEmpty()]
    [string]$DataBaseUser,
    [Parameter(Mandatory=$True)][ValidateNotNullOrEmpty()]
    [string]$DataBasePassword,
    [Parameter(Mandatory=$True)][ValidateNotNullOrEmpty()]
    [string]$Query
  )
	
  Try {

    $ConnectionString = "User Id=$DataBaseUser;Password=$DataBasePassword;Data Source=$DataBaseName"

    # Load library
    Add-Type -Path "C:\Oracle.ManagedDataAccess.dll"

    # Create connection
    $Connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($ConnectionString)

    # Open connection
    If($Connection.State -ne 'Open') {
      $Connection.Open()
    }
		
    # Create command
    $Command = $Connection.CreateCommand()

    # Set query for executing
    $Command.CommandText = $Query

    # Only for select queries
    if($Query -like 'SELECT*') {

      # Execute query
      $Reader = $Command.ExecuteReader()

      # Create object which will contain data from table
      $Fields = $Reader.FieldCount
      $Table  = New-Object System.Collections.ArrayList
   
      # Iterate through rows
      while($Reader.Read()) {

        $Row = New-Object System.Object
        # Iterate through fields inside each row
        for($i = 0; $i -lt $Fields; $i++) {
          $Row | Add-Member -MemberType NoteProperty -Name $Reader.GetName($i) -Value $Reader.GetValue($i)
        }
        # Add object to the array
        $Table.Add($Row) | Out-Null

      }

      # Create result object
      $Result = @{
        result = 0
        data = $Table
      }

    } else {
      
      # Execute query
      $Command.Transaction = $Connection.BeginTransaction()
      $Data = $Command.ExecuteNonQuery()

      # Create result object
      $Result = @{
        result = 0
        data = $Data
        transaction = $Command.Transaction
      }
       
    }

  } catch {
  
    # Get error message and return it
    $Exception = $_
    $Data = $Exception.Exception.Message
    If($Exception.Exception.Message -like '*ORA-*') {
        If($Exception.Exception.Message -match 'Exception.*: \"(.*)\"') { $Data = $Matches[1] }
    }
    
    # Create result object
    $Result = @{
      result = 1
      data = $Data
      transaction = $Command.Transaction
    }

  }

  return $Result

}

Теперь по порядку разберем, что происходит в скрипте.
Строка загружает библитеку и делает доступными для текущей сессии PowerShell классы, необходимые для манипуляций с базой данных.

    Add-Type -Path "C:\Oracle.ManagedDataAccess.dll"

Строка создает подключение к базе данных.

    $Connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($ConnectionString)

Строка открывает подключение к базе данных.

    $Connection.Open()

Строка создает команду.

    $Command = $Connection.CreateCommand()

Строка присваивает созданной команде строку запроса.

    $Command.CommandText = $Query

Строка выполняет запрос для select. После этого идет обработка результата, чтобы на выходе получить таблицу в удобной форме.

    $Reader = $Command.ExecuteReader()

Эти строки выполняют все запросы кроме select. Выполняя запрос таким образом, создается транзакция, которую затем необходимо подтвердить или откатить.

    $Command.Transaction = $Connection.BeginTransaction()
    $Data = $Command.ExecuteNonQuery()

Чтобы выполнить commit или rollback, надо выполнить такие строки:

    $Result = Send-OracleQuery -DataBaseName 'dbname' -DataBaseUser 'username' -DataBasePassword 'P@ssw0rd' -Query "update table set column1='value1' where column1='value2'"
    # Чтобы сохранить результат запроса
    $Result.Transaction.Commit()
    # Чтобы отменить результат запроса
    $Result.Transaction.Rollback()

Затем идет обработка исключений и возврат результата.