question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked ChristopherJack-1763 answered

Query Oracle Database using Powershell

Hi,

I have the oracle client installed on my machine and I am using the following code to query the oracle database

 ### 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
 }
 ### connection string ###
 $OracleConnectionString = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=$servicename)));uid=user;pwd=password;"
    
 ### open up oracle connection to database ###
 $OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString);
 $OracleConnection.Open()
    
 try {
    
     ### sql query command ###
     $OracleSQLQuery = "SELECT * FROM charge_code"
    
     ### create object ###
     $SelectCommand1 = New-Object System.Data.OracleClient.OracleCommand;
     $SelectCommand1.Connection = $OracleConnection
     $SelectCommand1.CommandText = $OracleSQLQuery
     $SelectCommand1.CommandType = [System.Data.CommandType]::Text
    
     ### create datatable and load results into datatable ###
     $SelectDataTable = New-Object System.Data.DataTable
     $SelectDataTable.Load($SelectCommand1.ExecuteReader())
    
 }
 catch {
    
     Write-Host "Error while retrieving data!"
    
 }

It is retrieving the data and putting it in $SelectDataTable

However I ma not sure how to access the data .. there is

$SelectDataTable.Colums[0] which for example contains the column header.

However... I cant seem to locate where the data is stored in the object?

Any help appreciated.



windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 answered

Managed to find the data in

$SelectDataTable[0].Rows


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.