Public/Invoke-OracleCmd.ps1

function Invoke-OracleCmd {
  <#
    .SYNOPSIS
    Invoke Oracle SQL command

    .DESCRIPTION
    Run a SQL command on an Oracle database

    .NOTES
    File name: Invoke-OracleCmd.ps1
    Author: Florian Carrier
    Creation date: 2020-02-04
    Last modified: 2020-02-06
    Dependencies: Invoke-OracleCmd requires Oracle Data Provider for .NET

    .LINK
    https://www.powershellgallery.com/packages/PSTK

    .LINK
    Invoke-SqlCmd

    .LINK
    https://www.oracle.com/database/technologies/appdev/dotnet/odp.html

    .LINK
    https://www.nuget.org/packages/Oracle.ManagedDataAccess.Core
  #>

  [CmdletBinding (
    SupportsShouldProcess = $true
  )]
  Param (
    [Parameter (
      Position    = 1,
      Mandatory   = $true,
      HelpMessage = "Name of the database host"
    )]
    [ValidateNotNullOrEmpty ()]
    [Alias ("Server")]
    [System.String]
    $Hostname,
    [Parameter (
      Position    = 2,
      Mandatory   = $true,
      HelpMessage = "Database server port number"
    )]
    [ValidateNotNullOrEmpty ()]
    [System.String]
    $PortNumber,
    [Parameter (
      Position    = 3,
      Mandatory   = $true,
      HelpMessage = "Name of the Oracle service"
    )]
    [ValidateNotNullOrEmpty ()]
    [System.String]
    $ServiceName,
    [Parameter (
      Position    = 4,
      Mandatory   = $true,
      HelpMessage = "SQL query"
    )]
    [ValidateNotNullOrEmpty ()]
    [System.String]
    $Query,
    [Parameter (
      Position          = 5,
      Mandatory         = $false,
      HelpMessage       = "Database user credentials",
      ParameterSetName  = "Credentials"
    )]
    [ValidateNotNullOrEmpty ()]
    [System.Management.Automation.PSCredential]
    $Credentials,
    [Parameter (
      Position          = 5,
      Mandatory         = $false,
      HelpMessage       = "User name",
      ParameterSetName  = "UserPassword"
    )]
    [ValidateNotNullOrEmpty ()]
    [Alias ("Name")]
    [System.String]
    $Username,
    [Parameter (
      Position          = 6,
      Mandatory         = $false,
      HelpMessage       = "Password",
      ParameterSetName  = "UserPassword"
    )]
    [ValidateNotNullOrEmpty ()]
    [Alias ("Pw")]
    [System.String]
    $Password,
    [Parameter (
      Mandatory   = $false,
      HelpMessage = "Connection timeout (in seconds)"
    )]
    [ValidateNotNullOrEmpty ()]
    [System.Int32]
    $ConnectionTimeOut,
    [Parameter (
      Mandatory   = $false,
      HelpMessage = "Query timeout (in seconds)"
    )]
    [ValidateNotNullOrEmpty ()]
    [System.Int32]
    $QueryTimeOut,
    [Parameter (
      HelpMessage = "Abort on error"
    )]
    [Switch]
    $AbortOnError,
    [Parameter (
      HelpMessage = "Encrypt connection"
    )]
    [Switch]
    $EncryptConnection,
    [Parameter (
      HelpMessage = "Include SQL user errors"
    )]
    [Switch]
    $IncludeSqlUserErrors,
    [Parameter (
      HelpMessage = "Out SQL errors"
    )]
    [Switch]
    $OutputSqlErrors
  )
  Begin {
    # Get global preference variables
    Get-CallerPreference -Cmdlet $PSCmdlet -SessionState $ExecutionContext.SessionState
  }
  Process {
    # Define connection string
    $ConnectionString = "Data Source='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$Hostname)(PORT=$PortNumber))(CONNECT_DATA=(SERVICE_NAME=$ServiceName)))';"
    # Check authentication mode
    if ($PSBoundParameters.ContainsKey("Credentials")) {
      # If "secured" credentials are provided
      $ConnectionString = $ConnectionString + "User ID=$($Credentials.Username);Password=$($Credentials.GetNetworkCredential().Password);"
      $SensitiveData    = $Credentials.GetNetworkCredential().Password
    } elseif ($PSBoundParameters.ContainsKey("Username") -And $PSBoundParameters.ContainsKey("Password")) {
      # If plain text credentials are provided
      if ($Username) {
        $ConnectionString = $ConnectionString + "User ID=$Username;Password=$Password;"
        $SensitiveData    = $Password
      } else {
        Write-Log -Type "ERROR" -Message "Invalid username ""$Username""" -ExitCode 1
      }
    } else {
      # Else default to integrated security (Windows authentication)
      Write-Log -Type "DEBUG" -Message "Using Integrated Security"
      $ConnectionString = $ConnectionString + "Integrated Security=True;"
    }
    # Technical parameters (Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;)
    if ($PSBoundParameters.ContainsKey("ConnectionTimeOut") -And $ConnectionTimeOut -ne $null) {
      $ConnectionString = $ConnectionString + "Connection Timeout=$ConnectionTimeOut;"
    }
    # Create connection object
    Write-Log -Type "DEBUG" -Object $ConnectionString -Obfuscate $SensitiveData
    $Connection = New-Object -TypeName "Oracle.ManagedDataAccess.Client.OracleConnection" -ArgumentList $ConnectionString
    # Try to open the connection
    try {
      $Connection.Open()
    } catch {
      Write-Log -Type "ERROR" -Object "Unable to reach database $($Hostname):$PortNumber/$ServiceName"
      return $Error
    }
    # Create SQL command
    $Command = $Connection.CreateCommand()
    # TODO sanitize query
    $Command.CommandText = $Query
    Write-Log -Type "DEBUG" -Object $Command
    # Execute command
    try {
      $Reader = $Command.ExecuteReader()
    } catch {
      Write-Log -Type "ERROR" -Object "Could not execute statement`n$Query"
      return $Error
    }
    # Get result
    $Result = $Reader.Read()
    # Close connection
    $Connection.Close()
    # Check outcome
    if ($Result) {
      # TODO return actual result
      return $Result
    } else {
      return $null
    }
  }
}