Accessing Oracle

Exclusive offer: get 50% off this eBook here
Instant Oracle Database and PowerShell How-to [Instant]

Instant Oracle Database and PowerShell How-to [Instant] — Save 50%

Utilize the power of Microsoft's powerful scripting engine to automate database tasks with Oracle from PowerShell book and ebook.

$19.99    $10.00
by Geoffrey Hudik | March 2013 | Enterprise Articles Microsoft Oracle

In this article by Geoffrey Hudik, the author of Instant Oracle Database and PowerShell How-to , we will look at loading libraries to create objects to interact with Oracle.

(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:\Windows\assembly\GAC_32\Oracle.DataAccess \2.112.3.0__89b483f429c47342\Oracle.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:\Oracle\product\11.2.0\client_1\odp.net\bin\2.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_BASE\ORACLE_HOME\ODACDoc\DocumentationLibrary, 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%\SysWOW64\WindowsPowerShell\v1.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. NET\assembly 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:


Instant Oracle Database and PowerShell How-to [Instant] Utilize the power of Microsoft's powerful scripting engine to automate database tasks with Oracle from PowerShell book and ebook.
Published: January 2013
eBook Price: $19.99
See more
Select your format and quantity:

About the Author :


Geoffrey Hudik

Geoffrey Hudik is a developer with 14 years of experience. He has worked with a variety of companies including Alltel Information Services, JPMorgan Chase, TeamHealth, and Eventbooking.com. These days, he mostly works with C#, ASP.NET MVC and web technologies, Windows development, CTI, XAML, Oracle, SQL Server, PowerShell, Kinect, and mobile technologies including Windows Phone and iOS. He records his thoughts on his blog at http://geoffhudik.com/tech and on Twitter (@thnk2wn).

Books From Packt


Oracle Database 11gR2 Performance Tuning Cookbook
Oracle Database 11gR2 Performance Tuning Cookbook

Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial
Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial

OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide ( 1ZO-051 )
OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide ( 1ZO-051 )

Oracle 11g R1/R2 Real Application Clusters Essentials
Oracle 11g R1/R2 Real Application Clusters Essentials

Oracle APEX Best Practices
Oracle APEX Best Practices

Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting
Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting

Oracle 11g Anti-hacker's Cookbook
Oracle 11g Anti-hacker's Cookbook

Oracle Modernization Solutions
Oracle Modernization Solutions


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Y
M
8
k
9
Q
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software