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

18 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.

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

    Regards!

  5. 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.

  6. Did anyone get:

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

  7. 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.

  8. @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

  11. Greetings, thank you very much for the information, I have an error when starting the connection and I would like to know if you can help me, in the line of “Add-Type -Path” I add the path where the dll is found and I get the following error:

    Add-Type : Unable to load the file or assembly 
'file:///C:\app\user\product\11.2.0\client_2\ODP.NET\bin\2.x\Oracle.DataAccess.dll' ni una de sus dependencias. Se ha intentado cargar 
un programa con un formato incorrecto.
En línea: 1 Carácter: 1
+ Add-Type -Path "C:\app\jhsilvav\product\11.2.0\client_2\ODP.NET\bin\2 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Add-Type], BadImageFormatException
    + FullyQualifiedErrorId : System.BadImageFormatException,Microsoft.PowerShell.Commands.AddTypeCommand
 
New-Object : No se encuentra el tipo [Oracle.DataAccess.Client.OracleConnection]. Compruebe que está cargado el ensamblado que lo contiene.
En línea: 7 Carácter: 8
+ $con = New-Object Oracle.DataAccess.Client.OracleConnection($dbaConne ...
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
 
Error en la invocación del método porque [System.Data.SqlClient.SqlDataAdapter] no contiene ningún método llamado 'Open'.
En línea: 8 Carácter: 1
+ $con.Open();
+ ~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound
 
Error en la invocación del método porque [System.Data.SqlClient.SqlDataAdapter] no contiene ningún método llamado 'Close'.
En línea: 12 Carácter: 1
+ $con.Close();
+ ~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

  12. Is there a way to check if the connection between Powershell and the Oracle database failed to connect? Has anyone ever done anything like this?

  13. I built it as a function and created a test switch to check the connection if it was successful or not.

    I called the function Open-OracleConnection and run it like this
    Open-OracleConnection -sid -user -password -test

    There’s more to it obviously. Be sure to always close the connection when done.

    .....
    $connectionString = 'User id=' + $User + ';Password=' + $Password + `
    ';Data Source=' + $SID

    $Connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
    $connection.open()

    if ($test)
    {
    $Connection
    $Connection.Close()
    break;
    .....

    The output should show you values like State, if closed then something happened that caused the connection to fail. If successful you should see values for ServerVersion,InstanceName, and Hostname.

  14. I followed the same steps and getting PowerShell error,

    Exception calling “Open” with “0” argument(s): “ORA-12154: TNS:could not resolve the connect identifier specified”
    At C:\…..\Untitled3.ps1:8 char:1
    + $connection.open()
    + ~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OracleException

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

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

  16. I am unable to run SHUTDOWN IMMEDIATE. Will it require anything other than sysdba privileges which my user already has?

  17. @Feby I am with the same error

    Some help? Where does the TNSnames.ora file have to be?

Leave a Reply

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