(For more resources related to this topic, see here.)

Getting ready

Open a PowerShell session for the appropriate processor architecture, x86 or x64 according to your ODAC installation. See the preceding recipe, Setting up your environment (Simple) for more information. Often it is easiest to start with the PowerShell ISE (Integrated Scripting Environment) for a better editing and debugging experience.

How to do it…

1. The Oracle Data Provider for .NET (ODP.NET) is included with ODAC in Oracle. DataAccess.dll and is generally the best choice for accessing Oracle. The first step is loading this library into memory:

   $odpAssemblyName = "Oracle.DataAccess, Version=2.112.3.0,
   Culture=neutral, PublicKeyToken=89b483f429c47342"
   [System.Reflection.Assembly]::Load($odpAssemblyName)
   GAC Version Location
   --- ------- --------
   True v2.0.50727 C:WindowsassemblyGAC_32Oracle.DataAccess
   2.112.3.0__89b483f429c47342Oracle.DataAccess.dll

2. Once the assembly is loaded you can start creating types. If you are not sure what object types are available, use the following code to enumerate public types in the Oracle.DataAccess assembly.

   $asm = [appdomain]::currentdomain.getassemblies() | where-object
   {$_.FullName -eq $odpAssemblyName}
   $asm.GetTypes() | Where-Object {$_.IsPublic} | Sort-Object {$_.
   FullName } | ft FullName, BaseType | Out-String
   
   

3. With the assembly loaded, and the types to create known, you can now start creating objects. A logical place to start is with a connection object:

   $conn = New-Object Oracle.DataAccess.Client.OracleConnection

How it works…

First the .NET Framework’s Assembly.Load method is called and a fully qualified assembly name is specified. By default PowerShell 2.0 runs against the .NET Framework 2.0 so the 2.x version of Oracle.DataAccess is specified in $odpAssemblyName. The parts of the assembly name can be found by inspecting %WINDIR%assembly in Windows Explorer. Because the output was not explicitly captured or discarded, details of the loaded assembly are shown.

Next, the AppDomain.CurrentDomain.GetAssemblies method is invoked and the list is filtered down to the Oracle.DataAccess assembly. This verifies that the assembly loaded and provides a reference to it. Finally it gets the types in that assembly, narrows them down by public types, sorts the data by full name, and formats the resulting table with the full name and base type properties of each Oracle type that is available.

There’s more…

To discard the output of the assembly load, use either [void] or a pipe to Out-Null.

[System.Reflection.Assembly]::Load($odpAssemblyName) | Out-Null
[void][System.Reflection.Assembly]::Load($odpAssemblyName)

Alternatively, capture the output to a variable and format another way:

$odpAsm = [System.Reflection.Assembly]::Load($odpAssemblyName)
"Loaded Oracle.DataAccess from {0}" -f $odpAsm.Location

Here are three other ways of loading ODP.NET:

• [Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess") 
  $filename = "C:Oracleproduct11.2.0client_1odp.netbin2.x 
  Oracle.DataAccess.dll"

• [void][Reflection.Assembly]::LoadFile($filename)

• [void][Reflection.Assembly]::LoadFrom($filename)

LoadWithPartialName is convenient but blindly loads an unknown version of ODP.NET. Both LoadFile and LoadFrom can also lead to issues. See http://msdn.microsoft.com/en-us/library/dd153782.aspx for assembly loading best practices. Using Load with an explicit, full assembly identity is safest, despite more typing.

Now, let’s find out how to get help with ODP.NET, and some other ways of accessing Oracle.

Getting help with ODP.NET

Oracle’s ODP documentation can be accessed via the Start Menu, with Windows Explorer under ORACLE_BASEORACLE_HOMEODACDocDocumentationLibrary, or online at http://docs.oracle.com.

Also, PowerShell’s Get-Member (alias gm) is useful for inspecting available members of objects; for example, use $conn | gm to list all the properties and methods available on the OracleConnection that was created.

Loading ODP.NET 4.0

The ODAC installation installs two versions of Oracle.DataAccess, one for .NET Framework 2 and another for PowerShell 2.0, which was built before .NET Framework 4.0. As such, you cannot directly load Version 4.0 assemblies by default. Generally the Version 2.0 will suffice. If there are specific 4.0 features you want, you have a few options:

• Change a global registry setting that forces all .NET 2.0 assemblies to run under .NET 4 (from PowerShell or otherwise); this has a number of serious consequences and should almost always be avoided.

• Create or update powershell.exe.config and powershell_ise.config in $pshome (that is %WINDIR%SysWOW64WindowsPowerShellv1.0) for x86 and/or x64 to run PowerShell sessions under .NET 4.0; this is better than the first option, but still applies globally. See http://poshcode.org/2045 for details.

• Use custom script code to selectively run a given script block or file as a .NET 4.0 child process from within PowerShell’s .NET 2.0 context. This has the least potential impact but can make running and debugging more difficult. For example:https://gist.github.com/882528.

• Use PowerShell 3.0, which uses .NET 4.0 by default. This is the best option.

If PowerShell 3.0 is available that is the path of least resistance. Otherwise the PowerShell configuration modifications are most likely the next best route, unless you have various other PowerShell scripts or cmdlets (built-in or custom) that may not execute correctly when run under the .NET Framework 4.0 runtime.

Once configured, load the assembly as before but specify the 4.X version number.

$odpAssemblyName = "Oracle.DataAccess, Version=4.112.3.0,
Culture=neutral, PublicKeyToken=89b483f429c47342"
[System.Reflection.Assembly]::Load($odpAssemblyName)

To get the assembly name and other details here, you can open %WINDIR%Microsoft. NETassembly in Windows Explorer, navigate to Oracle.DataAccess.dll, and open the DLL in a dissembler such as dotPeek by JetBrains.

Using OLE DB

One alternative to ODP.NET is using OLE DB (included with ODAC). In this case the assembly is already loaded; just start creating the types.

$conn = New-Object System.Data.OleDb.OleDbConnection

The only real advantage of OLE DB is your scripts are more portable in that your data access script logic can be reused for databases other than Oracle. This comes at the cost of slow performance with COM interop, the loss of some Oracle optimizations, and less functionality than ODP.NET.

Using Microsoft’s Data Provider for Oracle

Microsoft’s .NET Framework Data Provider for Oracle (System.Data.OracleClient) is deprecated and generally should not be used. It does provide a more lightweight footprint than a full ODAC install but has a number of performance and functionality limitations and is not being maintained.

[System.Reflection.Assembly]::Load("System.Data.OracleClient,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
$conn = New-Object System.Data.OracleClient.OracleConnection

Summary:

Using an ODBC driver (included with ODAC) is an older technique but could be useful in conjunction with systems that do not support newer drivers. Another option is loading a .NET data access DLL from PowerShell. This could be either a standard .NET class library or one that references System.Management.Automation.dll and exposes cmdlets for friendly PowerShell usage.

You can also use Oracle’s SQL*Plus client from PowerShell to send commands and receive outputs via pipes. This book will focus primarily on using ODP.NET directly from PowerShell. Despite some heft, ODP.NET provides the best performance and functionality and staying in a PowerShell script context prevents more involved work of developing compiled DLLs.

Resources for Article :

Further resources on this subject:

• High Availability: Oracle 11g R1 R2 Real Application Clusters (RAC) [Article]
• Oracle Tools and Products [Article]
• Oracle Integration and Consolidation Products [Article]