POWERSHELL | QUERY ORACLE DATABASE

HERE IS A SCRIPT I WROTE TO QUERY AN ORACLE DATABASE INTO A DATASET AND THEN DISPLAY IN GRIDVIEW (OR OTHER OF YOUR CHOOSEING)

#Load Oracle Assembly

if ($Oracle_Loaded -eq $null) {$Oracle_Loaded = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")}

Function Execute_OracleCommand([string]$HostName, [string]$ServiceName, [int]$Port, [string]$SQLStatement, [string]$Login, [string]$Password, [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$OutputType="DataRow")

{

              $SQLStatement = $SQLStatement -replace "\`$",'$'   # DBA Views have $ in the name ex. v$Database

              $SQLStatement = $SQLStatement -replace "\`#",'#'   # Oracle likes to use # for primary foreign key relationships

              $ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) `

                                                (HOST=$HostName)(PORT=$Port)) `

                                                (CONNECT_DATA=(SERVICE_NAME=$ServiceName))) `

                                                ;User Id=$Login;Password=$Password;"

              $OracleConnection = new-object Oracle.DataAccess.Client.OracleConnection

        $OracleConnection.ConnectionString = $ConnectionString

        $OracleCommand = new-object Oracle.DataAccess.Client.OracleCommand($SQLStatement,$OracleConnection)

        $OracleCommand.CommandTimeout=0

        $DataSet = New-Object system.Data.DataSet

        $DataAdapter = New-Object Oracle.DataAccess.Client.OracleDataAdapter($OracleCommand)

        try

              {

                     $OracleConnection.Open()

                     $null = $DataAdapter.fill($DataSet)

                     $OracleConnection.close()

                     switch ($OutputType)

                  {

                      'DataSet'   { Write-Output ($DataSet) }

                      'DataTable' { Write-Output ($DataSet.Tables) }

                      'DataRow'   { Write-Output ($DataSet.Tables[0]) }

                  } 

              }

           catch

           {

                     $ex = $_.Exception

                     Write-Output $ex.Message

              }

              Finally

              {

                     #Close the connection even if it fails

                     $OracleConnection.Close()

              }

}

function Execute-NonQuery($SQLStatement)

{

        $OracleConnection = new-object Oracle.DataAccess.Client.OracleConnection

        $OracleConnection.ConnectionString = $ConnectionString

        $OracleCommand = new-object Oracle.DataAccess.Client.OracleCommand($SQLStatement,$OracleConnection)

        $OracleCommand.CommandTimeout=0

        try

              {

                     $OracleConnection.Open()

              $OracleCommand.ExecuteNonQuery()

                     $OracleConnection.Close()               

              }

           catch

           {

                     $ex = $_.Exception

                     Write-Output $ex.Message

              }

              Finally

              {

                     #Close the connection even if it fails

                     $OracleConnection.Close()

              }

}

Clear-Host

$HostName = "dss0s001.svr.us.jpmchase.net"

$ServiceName = "pistr.svr.us.jpmchase.net"

$Port = 1521

#You will get an arithmetic overflow error on integers (numeric) as they are out of bounds for .Net so cast them as Integers

$SQLStatement = 'SELECT

    CAST(Session_Key as int) as Session_Key

    ,CAST(Session_RecID as int) as Session_RecID

    ,CAST(Session_Stamp as int) as Session_Stamp

    ,Command_id

    ,Start_Time

    ,End_time

    ,CAST(Input_Bytes as int) as Input_Bytes

    ,CAST(Output_Bytes as int) as Output_Bytes

    ,CAST(Status_Weight as int) as Status_Weight

    ,CAST(Optimized_Weight as int) as Optimized_Weight

    ,CAST(OBJECT_TYPE_WEIGHT as int) as OBJECT_TYPE_WEIGHT

    ,OUTPUT_DEVICE_TYPE

    ,CAST(AUTOBACKUP_COUNT as int) as AUTOBACKUP_COUNT

    ,AUTOBACKUP_DONE

    ,STATUS

    ,INPUT_TYPE

    ,OPTIMIZED

    ,CAST(ELAPSED_SECONDS as int) as ELAPSED_SECONDS

    ,CAST(COMPRESSION_RATIO as int) as COMPRESSION_RATIO

    ,CAST(INPUT_BYTES_PER_SEC as int) as INPUT_BYTES_PER_SEC

    ,CAST(OUTPUT_BYTES_PER_SEC as int) as OUTPUT_BYTES_PER_SEC

    ,INPUT_BYTES_DISPLAY

    ,OUTPUT_BYTES_DISPLAY

    ,INPUT_BYTES_PER_SEC_DISPLAY

    ,OUTPUT_BYTES_PER_SEC_DISPLAY

    ,TIME_TAKEN_DISPLAY

FROM v$rman_backup_job_details;'

 

$Login = "oracle_user"

$Password = "mypassword"

Write-Host $SQLStatement

$Results = Execute_OracleCommand -HostName $HostName -ServiceName $ServiceName -Port $port -Login $Login -Password $Password -SQLStatement $SQLStatement -OutputType "DataRow"

$Results | Out-GridView