Oracle and Powershell return only the first line

Question

I have a problem with powershell and oracle.

This is my code

Add-Type -Path "C:\app\aasif\product\11.2.0\client_2\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("User Id=sys;Password=password;Data Source=myserver/oracle;DBA privilege=SYSDBA")

$con.Open()
$cmd=$con.CreateCommand()
$cmd.CommandText="select distinct owner from all_tables where table_name = 'mytable'"

$rdr=$cmd.ExecuteReader()

if ($rdr.Read()) {
  $rdr.GetString(0)
}

$con.Close()

When i execute this query directly with SQLPlus, i have :

RS123
RS456
RS789
RS741
RS963

With my powershell, i can't view all the data returned by the query, but only the first line.

RS123

How can i do this?

Thanks

pratik garg · Accepted Answer · 2013-05-21 13:55:48Z

2

I think in place of if($rdr.Read()) you have to write while($rdr.Read()) to read and work with all the value from the output of the query.

Actually I am very new in powershell but in general language we have to put this condition in loop.

answered May 21, 2013 at 13:55

pratik garg

3,3421 gold badge19 silver badges21 bronze badges

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

Comments

Andy Arismendi · Accepted Answer · 2013-05-22 04:43:51Z

1

CreateCommand() returns an OracleCommand. ExecuteReader() returns and OracleDataReader.

Here's and example of using the OracleDataReader class:

$OracleDataReader = $cmd.ExecuteReader()
while ($OracleDataReader.Read()) {
    $OracleDataReader.GetString(0)
}

Notice I used GetString. You'll need to use the data reader method appropriate for the data type of the column corresponding to your SQL query. The available methods are listed here. If you don't know what the data type is you can use GetFieldType() where the parameter is the column ID you want to check.

answered May 22, 2013 at 4:43

Andy Arismendi

53.2k17 gold badges113 silver badges130 bronze badges

Collectives™ on Stack Overflow

Oracle and Powershell return only the first line

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related