Database Reference
In-Depth Information
Other ways to access Oracle
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 topic 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.
Connecting and disconnecting (Simple)
In this recipe we will explore making connections to Oracle.
Getting ready
Start by gathering any database connection details such as server and user information.
The example connection string is against the HR sample database included with Oracle
Express. To connect to this database you may need to adjust the user ID and/or password in
the connection string according to how you set it up. Refer to the
Oracle Database Express
Edition Getting Started Guide
for more information.
How to do it...
The code in this section assumes that ODP.NET has already been loaded as described in the
preceding recipe. Let's look at connecting without using TNS names:
function Connect-Oracle([string] $connectionString = $(throw
"connectionString is required"))
{
$conn= New-Object Oracle.DataAccess.Client.OracleConnection($conn
ectionString)
$conn.Open()
Write-Output $conn
}
function Get-ConnectionString($user, $pass, $hostName, $port, $sid)
{
$dataSource = ("(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})
(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})))" -f $hostName, $port,
$sid)
Write-Output ("Data Source={0};User Id={1};Password={2};Connection
Search WWH ::
Custom Search