New-OracleConnection

This function will let you create a database connection object for connecting to an Oracle database. It supports the usual parameters; DatabaseServer, DatabaseName, Port and Credential and will return a MySqlConnection object.

If you have read my other functions for database connections (New-SqlConnection and New-MsSqlConnection), this one is a little bit different. It consists of two functions; one for creating a data source, and the function creating the database connection itself. This means you can use it both with tnsnames.ora or without (by creating you own data source string, and passing this to the New-OracleConnection function).

function New-OracleDataSource {
<#
.SYNOPSIS
Create a new Oracle Data Source string.
.DESCRIPTION
This function creates a new Oracle Data Source string. If you want
to connect to an Oracle database without the use of a data source
defined in TNSNAMES.ORA, you can use this function to generate
the string representation of the data source.
.EXAMPLE
$myDataSource = New-OracleDataSource -DBHost 'dbserver01' -ServiceName 'dbServiceName'
.NOTES
Author: Øyvind Kallstad
Date: 28.01.2015
Version: 1.0
#>
[CmdletBinding()]
param (
[Parameter(Position = 0, Mandatory = $true)]
[Alias('DatabaseServer','Server','ComputerName')]
[string] $DBHost,
[Parameter(Position = 1, Mandatory = $true)]
[Alias('DatabaseName','Name')]
[string] $ServiceName,
[Parameter()]
[ValidateRange(1,65535)]
[int] $Port = 1521,
[Parameter()]
[ValidateSet('ipc','nmp','sdp','tcp','tcps')]
[string] $Protocol = 'tcp'
)
Write-Output "(DESCRIPTION=(ADDRESS=(PROTOCOL=$($Protocol))(HOST=$($DBHost))(PORT=$($Port)))(CONNECT_DATA=(SERVICE_NAME=$($ServiceName))))"
}
function New-OracleConnection {
<#
.SYNOPSIS
Create a new Oracle database connection.
.DESCRIPTION
This function will create a new Oracle database connection and return a database connection object.
.EXAMPLE
$myDbConnection = New-OracleConnection -DataSource 'myDataSource'
.EXAMPLE
$myDbConnection = New-OracleConnection -DataSource (New-OracleDataSource -DBHost 'dbserver01' -ServiceName 'dbServiceName') -Credential $cred
.NOTES
Author: Øyvind Kallstad
Date: 28.01.2015
Version: 1.0
#>
[CmdletBinding()]
param (
# DataSource (usually configured in tnsnames.ora)
[Parameter(Mandatory = $true)]
[string] $DataSource,
# Credential, if not using trusted connection
[Parameter()]
[System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty
)
# start defining the connection string
$connectionString = "Data Source=$($DataSource)"
# if credential parameter is not used, trusted connection will be used
if (-not($PSBoundParameters['Credential'])) {
$connectionString += ';Integrated Security=yes'
}
# otherwise add username and password to the connection string
else {
$connectionString += ";User Id=$($Credential.UserName); Password=$(($Credential.GetNetworkCredential()).Password)"
}
Write-Verbose $connectionString
try {
[void][System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")
# create and open the database connection, and return the connection object
$dbConnection = New-Object TypeName Oracle.DataAccess.Client.OracleConnection ArgumentList $connectionString
[void]$dbConnection.Open()
Write-Output $dbConnection
}
# catching exceptions
catch {
Write-Warning Message "An unhandled exception occurred"
if ($_.Exception.InnerException){
Write-Warning $_.Exception.InnerException.Message
}
else{
Write-Warning $_.Exception.Message
}
}
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s