Unable to Get Information using PowerShell to Query Oracle

Question

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
'

arco444 · Accepted Answer · 2015-06-30 14:51:55Z

1

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

edited Jun 30, 2015 at 14:51

arco444

23k12 gold badges66 silver badges67 bronze badges

answered Jun 30, 2015 at 14:32

Little King

1,1201 gold badge8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to Get Information using PowerShell to Query Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related