CODEWITHSUNDEEP
databaseoracle-databasepowershell
Little King
Little King

Reputation: 1110

Unable to Get Information using PowerShell to Query Oracle

I use PowerShell to query SQL databases, and I am quite familiar with that process. However, I am now tasked with building an automated task that queries Oracle for information.

It seems straight forward: Install proper Oracle DLL's, import them into PS, execute the query much like SQL. However, this is not the case. All I get when I request information is a list called FieldCount. This seems to imply that I am able to see the information, it's just not displaying correctly. I'd like the actual values, and nothing seems to get this for me.

Thanks to anyone who knows anything about this, as my hands are tied and this is the only way I can think of to get this information from Oracle on a scheduled basis. I am not the Oracle admin, I only have read access to this view.

function Get-OracleData($cmdText){
    Add-Type -Path 'C:\app\client\username\product\12.1.0\client_1\odp.net\managed\common\Oracle.ManagedDataAccess.dll'
    $username = 'username'
    $password = 'password'
    $con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection('User Id=$username;Password=$password;Data Source=OracleServerName')
    $con.Open()
    $cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand
    $cmd.Connection  = $con
    $cmd.CommandText = $cmdText
    $rdr = $cmd.ExecuteReader()
    if($rdr.Read()){
        return $rdr
    }else{return 0}           
}
Get-OracleData -cmdText '
SELECT em.employee_number,
       em.last_name, 
       em.first_name,
       em.middle_names,
       em.email_address,
       em.start_date, 
       em.term_date,
       em.location_addr_line_1, 
       em.location_city, 
       em.location_work_state,
FROM CustomView em
'

Upvotes: 0

Views: 777

Answers (1)

Little King
Little King

Reputation: 1110

Found the answer in the link below. I was able to get what I needed by inserting the below code at the line where $cmd.CommandText = $cmdText is located in my original post, and getting rid of what's below it. 

$ds = New-Object system.Data.DataSet
$da = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($cmd)
[void]$da.fill($ds)
return $ds.Tables[0] | Select *

This returns to a variable, and I can get the first entry using $results[0], and $results[0].EMPLOYEE_NUMBER, etc.

Reference: http://poshcode.org/3965 @line55

Upvotes: 1

Related Questions