Invoke SQL Statments to MSSQL und ORACLE

invoke-sql.ps1

###########################################################################################################
#MS SQL
###########################################################################################################
<#
.SYNOPSIS
    Performs a SQL query and returns an array of PSObjects.
.NOTES
    Author: Jourdan Templeton - hello@jourdant.me
.LINK 
    https://blog.jourdant.me/post/simple-sql-in-powershell
#>
function Invoke-SqlCommand() {
    [cmdletbinding(DefaultParameterSetName="integrated")]
    Param (
        [Parameter(Mandatory=$true, Position = 0)][Alias("Serverinstance")][string]$Server,
        [Parameter(Mandatory=$true, Position = 1)][string]$Database,
        [Parameter(Mandatory=$true, ParameterSetName="not_integrated")][string]$Username,
        [Parameter(Mandatory=$true, ParameterSetName="not_integrated")][string]$Password,
        [Parameter(Mandatory=$false, ParameterSetName="integrated")][switch]$UseWindowsAuthentication = $true,
        [Parameter(Mandatory=$true, Position = 2)][string]$Query,
        [Parameter(Mandatory=$false)][int]$CommandTimeout=0
    )
    
    #build connection string
    $connstring = "Server=$Server; Database=$Database; "
    If ($PSCmdlet.ParameterSetName -eq "not_integrated") { $connstring += "User ID=$username; Password=$password;" }
    ElseIf ($PSCmdlet.ParameterSetName -eq "integrated") { $connstring += "Integrated Security=true;" }
    
    #connect to database
    $connection = [System.Data.SqlClient.SqlConnection]::new($connstring) #New-Object System.Data.SqlClient.SqlConnection($connstring)
    $connection.Open()
    
    #build query object
    $command = $connection.CreateCommand()
    $command.CommandText = $Query
    $command.CommandTimeout = $CommandTimeout
    
    #run query
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | out-null
    
    #return the first collection of results or an empty array
    If ($dataset.Tables[0] -ne $null) {$table = $dataset.Tables[0]}
    ElseIf ($table.Rows.Count -eq 0) { $table = New-Object System.Collections.ArrayList }
    
    $connection.Close()
    return $table
}

function Get-SqlTableNames() {
    [cmdletbinding(DefaultParameterSetName="integrated")]
    Param (
        [Parameter(Mandatory=$true, Position = 0)][Alias("Serverinstance")][string]$Server,
        [Parameter(Mandatory=$true, Position = 1)][string]$Database,
        [Parameter(Mandatory=$true, ParameterSetName="not_integrated")][string]$Username,
        [Parameter(Mandatory=$true, ParameterSetName="not_integrated")][string]$Password,
        [Parameter(Mandatory=$false, ParameterSetName="integrated")][switch]$UseWindowsAuthentication = $true,
        [Parameter(Mandatory=$false)][int]$CommandTimeout=0
    )
    
    $sqlCommand = "SELECT * FROM INFORMATION_SCHEMA.TABLES"
    If ($PSCmdlet.ParameterSetName -eq "not_integrated") { Invoke-SqlCommand -Server $Server -Database $Database -Username $Username -Password $Password -Query $sqlCommand -CommandTimeout $CommandTimeout }
    ElseIf ($PSCmdlet.ParameterSetName -eq "integrated") { Invoke-SqlCommand -Server $Server -Database $Database -UseWindowsAuthentication -Query $sqlCommand -CommandTimeout $CommandTimeout }
}

function Get-SqlTableColumns() {
    [cmdletbinding(DefaultParameterSetName="integrated")]
    Param (
        [Parameter(Mandatory=$true, Position = 0)][Alias("Serverinstance")][string]$Server,
        [Parameter(Mandatory=$true, Position = 1)][string]$Database,
        [Parameter(Mandatory=$true, Position = 2)][string]$TableName,
        [Parameter(Mandatory=$true, ParameterSetName="not_integrated")][string]$Username,
        [Parameter(Mandatory=$true, ParameterSetName="not_integrated")][string]$Password,
        [Parameter(Mandatory=$false, ParameterSetName="integrated")][switch]$UseWindowsAuthentication = $true,
        [Parameter(Mandatory=$false)][int]$CommandTimeout=0
    )
    $sqlCommand = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$TableName'"
    If ($PSCmdlet.ParameterSetName -eq "not_integrated") { Invoke-SqlCommand -Server $Server -Database $Database -Username $Username -Password $Password -Query $sqlCommand -CommandTimeout $CommandTimeout }
    ElseIf ($PSCmdlet.ParameterSetName -eq "integrated") { Invoke-SqlCommand -Server $Server -Database $Database -UseWindowsAuthentication -Query $sqlCommand -CommandTimeout $CommandTimeout }
}

###########################################################################################################
#ORACLE SQL
###########################################################################################################

Add-Type -Path ([System.IO.Path]::Combine($PSScriptRoot, "Oracle.ManagedDataAccess.dll"))

function Invoke-OracleSqlCommand() {
    [cmdletbinding()]
    Param (
        [Parameter(Mandatory=$true, Position = 0)][Alias("Serverinstance")][string]$Server,
        [Parameter(Mandatory=$true, Position = 1)][string]$Database,
        [Parameter(Mandatory=$true)][string]$Username,
        [Parameter(Mandatory=$true)][string]$Password,
        [Parameter(Mandatory=$true, Position = 2)][string]$Query,
        [Parameter(Mandatory=$false)][int]$CommandTimeout=0
    )

    $connectionString = 'User Id=' + $Username + ';Password=' + $Password + ';Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=' + $Server +')(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=' + $Database + ')))'
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
    $connection.open()
    $command=$connection.CreateCommand()
    $command.CommandText=$query


    #run query
    $adapter = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | out-null
    
    #return the first collection of results or an empty array
    If ($dataset.Tables[0] -ne $null) {$table = $dataset.Tables[0]}
    ElseIf ($table.Rows.Count -eq 0) { $table = New-Object System.Collections.ArrayList }
    
    $connection.Close()
    return $table
    
    $connection.Close()
}

function Get-OracleSqlTableNames() {
    [cmdletbinding()]
    Param (
        [Parameter(Mandatory=$true, Position = 0)][Alias("Serverinstance")][string]$Server,
        [Parameter(Mandatory=$true, Position = 1)][string]$Database,
        [Parameter(Mandatory=$true)][string]$Username,
        [Parameter(Mandatory=$true)][string]$Password,
        [Parameter(Mandatory=$false)][int]$CommandTimeout=0
    )
    $sqlCommand = "select * from sys.all_tables"
    Invoke-OracleSqlCommand -Server $Server -Database $Database -Username $Username -Password $Password -Query $sqlCommand
}

function Get-OracleSqlTableColumns() {
    [cmdletbinding()]
    Param (
        [Parameter(Mandatory=$true, Position = 0)][Alias("Serverinstance")][string]$Server,
        [Parameter(Mandatory=$true, Position = 1)][string]$Database,
        [Parameter(Mandatory=$true, Position = 2)][string]$TableName,
        [Parameter(Mandatory=$true)][string]$Username,
        [Parameter(Mandatory=$true)][string]$Password,
        [Parameter(Mandatory=$false)][int]$CommandTimeout=0
    )
    $sqlCommand = @"
    select *
    from sys.all_tab_columns col
    inner join sys.all_tables t on col.owner = t.owner 
    and col.table_name = t.table_name
    and col.table_name = '$TableName'
    order by col.column_id
"@
    Invoke-OracleSqlCommand -Server $Server -Database $Database -Username $Username -Password $Password -Query $sqlCommand
}