Tuesday, January 5, 2016

Creating a tnsnames.ora file using PowerShell

I recently began working with Oracle again after a long span of time and decided that I would make my life easier by trying to script setting up Oracle connections using my newfound knowledge of PowerShell.

Fortunately, I came across this article which accomplishes this rather handily: http://poshcode.org/1602

Of course, I wanted to modify the code to suit my needs a bit better to allow me to write the tnsnames.ora file to the required [Oracle Home]\network\admin\tnsnames.ora file path, so I modified the PowerShell script as follows:



[CmdletBinding()]
Param
(
 [Parameter(Mandatory=$true)]
 [string] $Name,
 [Parameter(Mandatory=$false,DontShow=$true)]
 [string] $Protocol = "TCP",
 [Parameter(Mandatory=$true)]
 [string] $Hostname,
 [Parameter(Mandatory=$true)]
 [string] $Port = "1521",
 [Parameter(Mandatory=$true)]
 [string] $Sid,
 [Parameter(Mandatory=$true,HelpMessage="Provide the tnsnames.ora file path")]
    [System.IO.FileInfo] $File
)

function Get-OracleDataSources
{
    [System.reflection.assembly]::LoadWithPartialName("System.Data")                                                  
    
    $f = [System.Data.Common.DbProviderFactories]::GetFactory("Oracle.DataAccess.Client")
    
    if ($f.CanCreateDataSourceEnumerator)
    {
        $e = $f.CreateDataSourceEnumerator()                                                                              
        $e.GetDataSources()
    }
}

function Out-TnsAdminFile
{
    param
    (
        [System.Object[]] $Entries,
        [System.IO.FileInfo] $File = $(throw "Parameter -File <System.IO.FileInfo> is required.")
    )
    
    begin 
    {
        if ($File.Exists)
        {
            $originalEntries = @(Get-TnsAdminEntries $File.FullName)
        }
    }
    
    process
    {
        if ($_)
        {
            $Entries = @($_)
        }
    
        $Entries | % {
            
            $entry = $_
            
            $existingEntry = $originalEntries | ? {$_.Name -eq $entry.Name}
            
            if ($existingEntry)
            {
                $existingEntry.Name = $entry.Name
                $existingEntry.Protocol = $entry.Protocol
                $existingEntry.Host = $entry.Host
                $existingEntry.Port = $entry.Port
                $existingEntry.Service = $entry.Service
            }
            else
            {
                $originalEntries += $entry
            }
        }
        
        $originalEntries | % {
        
            $entry = $_
        
            $Name = $entry.Name
            $Protocol = $entry.Protocol
            $Hostname = $entry.Host
            $Port = $entry.Port
            $Service = $entry.Service

            [string] $text += @"
$Name =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = $Protocol)(HOST = $Hostname)(PORT = $Port))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = $Service)
    )
)

"@
  }
  New-Item -Path $File.FullName -ItemType "File" -Force
  $text | Out-File $File.FullName -Encoding ASCII
        Remove-Variable text
    }
    
    end {}
}

function Get-TnsAdminEntries
{
    param
    (
        [System.IO.FileInfo] $File
    )
    
    begin {}
    
    process
    {
    
        if ($_)
        {
            $File = [System.IO.FileInfo] $_
        }
        if (!$File)
        {
            Write-Error "Parameter -File <System.IO.FileInfo> is required."
            break
        }
        if (!$File.Exists)
        {
            Write-Error "'$File.FullName' does not exist."
            break
        }
        
        [string] $data = gc $File.FullName | ? {!$_.StartsWith('#')}
        
        $pattern =  '(?<name>^(\w)+[\s]*?)|\)\)(?<name>\w+)|HOST=(?<host>\w+)|PORT=(?<port>\d+)|PROTOCOL=(?<protocol>\w+)|SERVICE_NAME=(?<service>\w+)'
        
        $patternMatches = [regex]::Matches($data.Replace(" ", ""), $pattern, [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)
        
        $tnsEntries = @()
        
        for ($i = 0; $i -lt $patternMatches.Count; $i++)
        {
            if ($i -eq 0 -or $i % 5 -eq 0)
            {
                $tnsEntry = New-Object System.Object
                $tnsEntry | Add-Member -type NoteProperty -name Name     -value $patternMatches[$i + 0].Groups["name"].value
                $tnsEntry | Add-Member -type NoteProperty -name Protocol -value $patternMatches[$i + 1].Groups["protocol"].value
                $tnsEntry | Add-Member -type NoteProperty -name Host     -value $patternMatches[$i + 2].Groups["host"].value
                $tnsEntry | Add-Member -type NoteProperty -name Port     -value $patternMatches[$i + 3].Groups["port"].value
                $tnsEntry | Add-Member -type NoteProperty -name Service  -value $patternMatches[$i + 4].Groups["service"].value
                
                $tnsEntries += $tnsEntry
            }
        }
        $tnsEntries
    }
    
    end {}
}

$tnsEntry = New-Object System.Object

$tnsEntry | Add-Member -type NoteProperty -name Name     -value $Name
$tnsEntry | Add-Member -type NoteProperty -name Protocol -value $Protocol
$tnsEntry | Add-Member -type NoteProperty -name Host     -value $Hostname
$tnsEntry | Add-Member -type NoteProperty -name Port     -value $Port
$tnsEntry | Add-Member -type NoteProperty -name Service  -value $Sid

$tnsEntry | Out-TnsAdminFile -File $File

1 comment:

  1. The blog you posted on powershell and creating a tnsnames.ora file is now a great help to me! It’s always a relief when there are detailed tutorials that simplify what might appear as complex processes. While reading through the blog, I could not help but reflect on the advantages of using PowerShell for diverse purposes. Like a lightweight motorcycle trailer, it is compact, efficient and designed for specific purposes. Like some handy motorcycle trailers that won’t make you feel like you are hauling around an entire metal shop everywhere you go, PowerShell seems like a nice way to handle such tasks as modifying tnsnames.ora files. The step-by-step guide provided by you makes it possible for any beginner to get access even if they are just beginning with PowerShell.

    ReplyDelete