Go Premium for a chance to win a PS4. Enter to Win


Powershell Script to Create DB Connection and retrieve data

Posted on 2014-02-06
Medium Priority
Last Modified: 2014-02-06
Need help in establishing OLEDB connection to the database, i am using the below script to connect to a oracle database, When i run the script the output is not showing all the records/ data. Enclosed my output window at the bottom.

$connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=XXXXXXX)(Port=1535)))(CONNECT_DATA=(SID=ABCD)));User ID=giltest;Password=XXXXXXXX"

$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)


$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)


$productNames = $command.ExecuteScalar();

write-host "List of the Products:" $productNames | ft


Question by:Jagadeeps
LVL 71

Accepted Solution

Qlemo earned 2000 total points
ID: 39840608
ExecuteScalar only returns a single result (one column, one row). You'll have to use ExecuteReader instead, with some more code added:
Add-Type -Assembly System.Data.OracleClient
$connectionString = "Data Source=XXXXXXX:1535/ABCD; User ID=giltest; Password=XXXXXXXX"

$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)

$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
reader = $command.ExecuteReader();

productNames = $(
  while ($reader.Read())

write-host "List of the Products:" $productNames | ft


# Add-Type -Assembly System.Data.OraOLEDB.Oracle

Open in new window

Note that I also changed to the PS 2.0 approach of loading assemblies. and used an EZConnect string instead of the old-fashioned connection description.
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39840612
Replace ExecuteScalar with ExecuteReader

Scalar returns only one data while reader returns multiple data

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question