My First PowerShell Module – PSOraenv

Update (04/01/2019): We’ve hit over 100 downloads for PSOraenv! A big thanks to the PowerShell community. Please keep using it and let me know how I can make it better.

*******

Most of my experience with Oracle has been on Linux, but recently I began working with it on Windows as well. It came to my attention very quickly that oraenv, my beloved friend in Oracle administration, is not present on Windows installations. When you have more than one database installed on a single server, or perhaps ASM (which has its own Oracle home), manually swapping back and forth to work with the different pieces gets really old really fast. For this reason, and because I’ve been anxious to learn how to make my own PowerShell modules, I created PSOraenv. Its purpose is to closely mirror the capabilities of oraenv and allow a similar level of simple, yet powerful, command-line flexibility on Windows systems. Let me give you some examples.

By default, the necessary environment variables for Oracle aren’t set when you fire up PowerShell or Command Prompt. (It seems to default to the most recently installed product.) Using my cmdlet Get-OraEnv, we can verify that they start empty.

In order to see what options are available to us, we run my cmdlet Get-OraSID to pull back a list of the database SID’s and associated Oracle homes on the local machine.

Now that we know what SID’s are available to us, my cmdlet Set-OraEnv can be used.

Now, if we run Get-OraEnv again, we can see that the environment variables have indeed been set.

And, just to prove that actually matters, sqlplus can verify which database we are currently working with. (My sandbox system defaults to lkftest2.)

And there you have it! We have easily viewed which databases are on the local server and quickly swapped to the one that’s needed. It’s just as easy to swap back to the lkfowler2 SID if needed.

This module can be installed from PowerShell Gallery using Install-Module, like the example below.

Install-Module -Name PSOraenv

(You can also find the PowerShell Gallery page at https://www.powershellgallery.com/packages/PSOraenv/1.0)

If you’re interested to see what’s in the code, that has been placed below. I welcome any comments, feedback, and functionality requests. Hope you enjoy!

<# 
 .Synopsis
  Show a list of Oracle SID's.

 .Description
  Show a list of Oracle SID's and their associated ORACLE_HOME values, pulled from the registry of the local machine.

 .Example
  Get-OraSID
#>

function Get-OraSID 
{
    #Create an empty object for display
    $display = @()

    #Get a list of Oracle homes from the registry
    $homes = Get-ChildItem -Path HKLM:\SOFTWARE\Oracle | where {$_.Name -match 'KEY_Ora'}

    #Loop through each home and add the desired information to the display object
    foreach ($path in $homes)
    {
        $dir = Split-Path $path.Name -Leaf
        $oraObject = Get-ItemProperty "HKLM:\SOFTWARE\Oracle\$dir"
      
        $objDisplay = New-Object System.Object
        $objDisplay | Add-Member -type NoteProperty -name OracleSid -value $oraObject.ORACLE_SID
        $objDisplay | Add-Member -type NoteProperty -name OracleHome -value $oraObject.ORACLE_HOME

        $display += $objDisplay
    }

    #Display the completed display object
    $display
}

<# 
 .Synopsis
  Displays the current value of your Oracle environment variables.

 .Description
  Displays the current value of ORACLE_SID, ORACLE_HOME, and ORACLE_BASE.

 .Example
  Get-OraEnv

#>
function Get-OraEnv
{
    #If ORACLE_SID has a value, display it
    if ($env:ORACLE_SID -ne $null)
    {
        Write-Host "ORACLE_SID: " $env:ORACLE_SID
    }

    else
    {
        Write-Host "ORACLE_SID  is empty"
    }

    #If ORACLE_HOME has a value, display it
    if ($env:ORACLE_HOME -ne $null)
    {
        Write-Host "ORACLE_HOME: " $env:ORACLE_HOME
    }

    else
    {
        Write-Host "ORACLE_HOME is empty"
    }

    #If ORACLE_BASE has a value, display it
    if ($env:ORACLE_BASE -ne $null)
    {
        Write-Host "ORACLE_BASE: " $env:ORACLE_BASE
    }

    else
    {
        Write-Host "ORACLE_BASE is empty"
    }
}

<# 
 .Synopsis
  Sets the Oracle environment variables based on a provided SID.

 .Description
  Sets the Oracle environment variables based on a provided SID.
  Information is obtained from the registry to set ORACLE_SID, ORACLE_HOME, and ORACLE_BASE.

 .Example
  # Set the current environment for the database DB1.
   Set-OraEnv DB1

#>
function Set-OraEnv 
{
param(
    [Parameter(Mandatory=$true)]
    [string]$oraSid
    )
    
    #Get a list of Oracle homes from the registry
    $homes = Get-ChildItem -Path HKLM:\SOFTWARE\Oracle | where {$_.Name -match 'KEY_Ora'}

    #Loop through each home to find the one we're working with based on the provided SID.
    #Then use the key's values to set our environment variables.
    foreach ($path in $homes)
    {
        $dir = Split-Path $path.Name -Leaf
        
        $oraObject = Get-ItemProperty "HKLM:\SOFTWARE\Oracle\$dir"
        
        if ($oraObject.ORACLE_SID -eq $oraSid)
        {
            $oraHome = $oraObject.ORACLE_HOME
            $oraBase = $oraObject.ORACLE_BASE
            
            Write-Host "Changing environment to:"
            Write-Host "ORACLE_SID : " $oraSid
            Write-Host "ORACLE_HOME: " $oraHome
            Write-Host "ORACLE_BASE: " $oraBase

            $env:ORACLE_SID = $oraSid
            $env:ORACLE_HOME = $oraHome
            $env:ORACLE_BASE = $oraBase
        }
    }  
}

#Make our functions available for use
export-modulemember -function Get-OraSID
export-modulemember -function Get-OraEnv
export-modulemember -function Set-OraEnv