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).
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 | |
| } | |
| } | |
| } |
Communary 