Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment