Oracle Database Queries in PowerShell, Script Examples

Below are some code examples to help get your data out of an Oracle database into a PowerShell object quickly! The logic to manipulate data is the same regardless of vendor: load any required libraries, define the connection string, setup the connection object, use that connection object for subsequent queries and finally close the connection.

Oracle Data Access Components (ODAC) allows you to run an Oracle complied binary within the .NET framework including PowerShell. When you are working with Oracle queries in PowerShell, it is required to download and install the Oracle Data Access Components prior to accessing a database. Select the appropriate architecture (x86/x86-64) and ensure the correct PowerShell program architecture is being executed with the corresponding Oracle component’s architecture. Failure to do so will lead to binary related errors while loading the assembly in PowerShell.

Load the System.Data.OracleClient Assembly:

### try to load assembly, fail otherwise ###
$Assembly = [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

if ( $Assembly ) {
    Write-Host "System.Data.OracleClient Loaded!"
}
else {
    Write-Host "System.Data.OracleClient could not be loaded! Exiting..."
    Exit 1
}

Setup the connection string and open a database connection:

### connection string ###
$OracleConnectionString = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.domain.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oracledb.domain.com)));uid=user;pwd=password;"

### open up oracle connection to database ###
$OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString);
$OracleConnection.Open()

Stored procedure example with parameters example:

try {

    ### create object ###
    $GetHostIDCommand = New-Object System.Data.OracleClient.OracleCommand;
    $GetHostIDCommand.Connection = $OracleConnection
    $GetHostIDCommand.CommandText = "GET_HOST_ID"
    $GetHostIDCommand.CommandType = [System.Data.CommandType]::StoredProcedure

    ### add storeprocedure paramaters ###
    $GetHostIDCommand.Parameters.Add("p_hostname", [System.Data.OracleClient.OracleType]::VarChar).Value = $Computer.ToUpper();
    $GetHostIDCommand.Parameters.Add("p_host_id", [System.Data.OracleClient.OracleType]::Number).Direction = [System.Data.ParameterDirection]::Output

    ### execute storedprocedure ###
    $GetHostIDCommand.ExecuteNonQuery() | Out-Null

    ### get the data from output defined by the storedprocedure ###
    $HostID = $GetHostIDCommand.Parameters["p_host_id"].Value

    ### dispose of object ###
    $GetHostIDCommand.Dispose()

}
catch { Write-Host "$Computer : Failed to GET_HOST_ID" }

SQL select query text example:

try {

    ### sql query command ###
    $OracleSQLQuery = "SELECT * FROM HOSTS"

    ### create object ###
    $SelectCommand = New-Object System.Data.OracleClient.OracleCommand;
    $SelectCommand.Connection = $OracleConnection
    $SelectCommand.CommandText = $OracleSQLQuery
    $SelectCommand.CommandType = [System.Data.CommandType]::Text

    ### create datatable and load results into datatable ###
    $SelectDataTable = New-Object System.Data.DataTable
    $SelectDataTable.Load($SelectCommand.ExecuteReader())

}
catch {

    Write-Host "Error while retrieving data!"

}

SQL update command text example:
(same would be for an insert command with no expected output)

try {

    ### sql update command query ###
    $HostUpdateHostSQL = "UPDATE HOSTS h SET h.PHYSICAL = '$(Convert-Boolean($HW_PHYSICAL))' WHERE h.ID = '$HostID'"

    $HostUpdateCommand = New-Object System.Data.OracleClient.OracleCommand;
    $HostUpdateCommand.Connection = $OracleConnection
    $HostUpdateCommand.CommandType = [System.Data.CommandType]::Text
    $HostUpdateCommand.CommandText = $HostUpdateHostSQL

    ### execute update command query ###
    $HostUpdateCommand.ExecuteNonQuery() | Out-Null

}
catch {

    ### output exception to screen ###
    Write-Host "$Hostname : ERROR! $HostUpdateHostSQL"
    Write-Host "Dump : $($_.Exception.ToString())"

}
### dispose regardless ###
finally { $HostUpdateCommand.Dispose() }