Using PowerShell to query Oracle DB’s without using the Oracle Client – Oracle Data Provider for .NET

With every Identity and Access Management project comes the often tactical integration with heritage/legacy systems that can often assist with their decommissioning. That is exactly what I was having to do a couple of weeks ago with Oracle. My public frustration with installing the Oracle Client on a Windows Server 2016 host to allow me to integrate Microsoft Identity Manager with Oracle saw me rewarded with an unsolicited but fantastic response from Sylvan Laurence. The suggestion was to use the Oracle Data Provider for .NET The key benefit here is NO Oracle Client Install Required, and I can leverage the library with PowerShell.

Oracle Data Provider for .NET Tweet.PNG
Oracle Data Provider for .NET Tweet

Being pointed in the right direction by Sylvan got me out of the rabbit hole I was in (although I did have the Oracle Client installed configured and working with MIM) and investigating how I could get the custom integration I required easier and quicker. This post details how to quickly use PowerShell to connect to an Oracle database without requiring the Oracle Client by leveraging the Oracle Data Provider for .NET library.

Installing the Oracle Data Provider for .NET

The Oracle Data Provider for .NET can be obtained from Oracle here. But for a version with an installer that we can then leverage with PowerShell the latest version of the 32-bit installer package is ODAC 12.2c Release 1 (12.2.0.1.0) and available here and the 64-bit installer packaged is ODAC 12.2c Release 1 (12.2.0.1.0) available here. You will need to register a free Oracle account to be able to download it. I’m using the 64-bit ODP.NET Managed ODAC122cR1.zip version. Expand the archive to a temp directory.

As per Sylvan’s guidance the installation process is;

  • from an elevated command prompt
    • install_odm.bat c:\OracleDAC x64 true
Oracle Data Provider for .NET Installation.PNG
Oracle Data Provider for .NET Installation

Configuring the Oracle Data Provider for .NET

Just as if you’d installed the full Oracle Client, the Oracle Data Provider for .NET leverages the SQLNET.ora and TNSNames.ora configuration files. Samples are provided under the yourInstallPath\network\admin\sample directory. Your configuration files need to be in the yourInstallPath\network\admin directory.

Here are examples of mine.

sqlnet.ora

Check with the DBA team maintaining the environment but chances are no changes are required here.

# sqlnet.ora Network Configuration File: 

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tnsnames.ora

Update for the Alias of the Oracle environment you are connecting to (available from the DBA team maintaining the environment). Mine is named IDM below. Likewise the FQDN of the Host and the Port it is configured to listen on.

# tnsnames.ora Network Configuration File
IDM =
   (DESCRIPTION =
       (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-db1.customer.com.au)(PORT = 16001))
       )
       (CONNECT_DATA =
          (SERVICE_NAME = IDM)
       )
   )

Hello World Query

Below is a Hello World query using PowerShell and ODP.NET. The query in line 12 should work against any Oracle SQL DB because it uses the built in table “dual”.

Update:

  • Line 2 if you installed ODP.NET to a different path
  • Line 6 for your SQL DB Username
  • Line 7 for your SQL DB Users Password
  • Line 8 for your SQL DB Alias (that matches what you have in your TNSNames.ora configuration file)

The screenshot below shows the output from running the Hello World Query.

Oracle Data Provider for .NET Query Result.PNG
Oracle Data Provider for .NET Query Result

Calling a Stored Procedure

The example above uses a simple SQL Query Select ‘Hello world!’ Greeting from dual” but what if you wanted to call a Stored Procedure? If you are just calling a Stored Procedure with no input or output you can update the CommandType to ‘Stored Procedure’

$command.CommandType = 'Stored Procedure'

and change the execution from ExecuteReader() to ExecuteNonQuery()

$reader = $command.ExecuteNonQuery()

Your QueryStatement is the name of your Stored Procedure. Successful execution of the Stored Procedure will return -1

If however you need to do something more complex you need to fallback to CommandType = ‘Text’, and define your SQL Call including the Stored Procedure like this;

$queryStatment = @"
DECLARE
   result varchar2(100);
   error varchar2(100);
BEGIN
   your.stored.procedure('$($any)', '$($variables)', '$($tobepassed)', result, error);
END;
"@

Successful execution of the Stored Procedure will return -1

A full example of that is shown below.

The screenshot below shows successful execution of a Stored Procedure as per the script above.

Oracle Data Provider for .NET Stored Procedure Execution Result.PNG
Oracle Data Provider for .NET Stored Procedure Execution Result

Summary

Using the Oracle Data Provider for .NET (ODP.Net) Oracle library we can use PowerShell to integrate with Oracle Databases executing queries and stored procedures. If you are looking to do something similar also checkout this Microsoft Devblog Post.