Querying an Oracle database from Powershell

I needed to query Oracle for information to use in a Powershell script. I found the following comprehensive blog post with details on how to do so using ODP.NET: Use Oracle ODP.NET and PowerShell to Simplify Data Access

The article is extremely long and goes into a lot of depth. The following is a short summary of the first steps, with just enough to get started:

  1. Download Oracle Data Provider for .NET (ODP.NET). (If that link doesn’t work just search for “Oracle ODP.NET”.)
    • Select “Download the latest ODP.NET production release.”
    • Select “64-bit ODAC Downloads”
    • Select “ODP.NET_Managed_ODAC12cR4.zip”
  2. Extract the ZIP file to C:\, which creates C:\ODP.NET_Managed_ODAC12cR4.
  3. Run cmd as administrator, navigate to C:\ODP.NET_Managed_ODAC12cR4, and run:
    install_odpm.bat C:\oracle\instantclient_10_2 both

In Powershell, add the DLL and set up a database connection and a query:

Add-Type -Path "C:\Users\chris\ODP.NET_Managed_ODAC12cR4\odp.net\managed\common\Oracle.ManagedDataAccess.dll"
$username = Read-Host -Prompt "Enter database username"
$password = Read-Host -Prompt "Enter database password"
$datasource = Read-Host -Prompt "Enter database TNS name"
$query = "SELECT first_name, last_name FROM users.user WHERE first_name = 'Chris' ORDER BY last_name"
$connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
$connection.open()
$command=$connection.CreateCommand()
$command.CommandText=$query
$reader=$command.ExecuteReader()
while ($reader.Read()) {
$reader.GetString(1) + ', ' + $reader.GetString(0)
}
$connection.Close()

Output of the above:

Brown, Chris
Carter, Chris
Jones, Chris
Smith, Chris

10 thoughts on “Querying an Oracle database from Powershell”

  1. sorry if it’s a dumb question, but what exactly is the data source? i downloaded oracle XE and spun up a database, but i’m not sure how to specify the data source to connect to it..

  2. Hi Billy Crystal.
    The data source, the instance name for your oracle instance you want to connec to.

    Nice post, I will try to use it.

  3. Thanks for this!!! I can successfully query an Oracle DB because you started me down the right path.

    Regards!

  4. Beware. If your database employs encryption this won’t work. ODP.NET shipped in a broken state and cannot support databases which use encryption. Don’t waste a day on this like I did.

  5. Did anyone get:

    Exception calling “GetString” with “1” argument(s): “Specified cast is not valid.”

  6. Disregard my previous question. I found the issue. One of my table columns was not a string and I wasn’t aware (didn’t set the table up and this is the first time I’ve used powershell to read from an Oracle database).

    Next question, what does $reader.read() do? I’m trying to comment my script and need to understand the purpose of this snippet of code.

  7. @Josh, the Read() method [.NET is not, apparently, case-sensitive] advances the Reader object to the next record returned by the query. Before it is called the first time, the Reader object is not yet pointing to any records. If Read() is called and there are no further records, it returns false (otherwise it returns true).

    The full documentation for the OracleDataReader.Read() method is available at https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledatareader.read(v=vs.110).aspx

Leave a Reply

Your email address will not be published. Required fields are marked *