Powershell script v3 to query against Oracle having an error v2

Hi EE,

I have the following script:

[Reflection.Assembly]::LoadFile("D:\app\icameron\product\11.2.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll")

$userId = ''
$password = ''
$host2 = ''
$port = '1521'
$serviceName = ''

$constr = "User Id=$userId;Password=$password;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host2)(PORT=$port))(CONNECT_DATA=(SERVICE_NAME=$serviceName)))"

$conn= New-Object Oracle.DataAccess.Client.OracleConnection($constr)
$conn.Open()
$sql="select * from dba_users"
$command = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$reader=$command.ExecuteReader()

while($reader.Read()){
  $reader.GetString(0)
}

$conn.Close()

Select allOpen in new window

When I run it get the error:


GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\Windows\assembly\GAC_64\Oracle.DataAccess\2.112.1.0__89b483f429c47342\Oracle.DataAccess.dll
Exception calling "Open" with "0" argument(s): "ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor"
At C:\psscripts\CernerQuery.ps1:12 char:1
+ $conn.Open()
+ ~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OracleException

Exception calling "ExecuteReader" with "0" argument(s): "Connection must be open for this operation"
At C:\psscripts\CernerQuery.ps1:15 char:1
+ $reader=$command.ExecuteReader()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

You cannot call a method on a null-valued expression.
At C:\psscripts\CernerQuery.ps1:17 char:7
+ while($reader.Read()){
+       ~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Any assistance is appreciated.

Thank you.