Execute-SQL for Oracle Database in powershell

Execute-SQL for Oracle Database in powershell.md

How to run Execute-SQL like commandlets for powershell to acces an oracle database

Insert the below code snippet to your $HOME\Documents\WindowsPowerShell\profile.ps1 file:

add-type -AssemblyName System.Data.OracleClient

<#
.Synopsis
   Execute SQL Statements in an Oracle Database
.DESCRIPTION
   Execute SQL Statements in an Oracle Database
.EXAMPLE
   Execute-OracleSQL -UserName "myUser" -Password "myPass" -DataSource "myDatasource" -Statement "Select 'Hello world! from dual'
#>
function Execute-OracleSQL
{
    [CmdletBinding()]
    [Alias()]
    [OutputType([int])]
    Param
    (
        # UserName required to login
        [string]
        [Parameter(Mandatory=$true,
                   ValueFromPipelineByPropertyName=$true)]
        $UserName,

        # Password required to login 
        [string]
        [Parameter(Mandatory=$true,
                   ValueFromPipelineByPropertyName=$true)]
        $Password,

        # DataSource (This is the TNSNAME of the Oracle connection)
        [string]
        [Parameter(Mandatory=$true,
                   ValueFromPipelineByPropertyName=$true)]
        $DataSource,

        # SQL Statement to execute.
        [string]
        [Parameter(Mandatory=$true,
                   ValueFromPipelineByPropertyName=$true)]
        $Statement
    )

    Begin
    {
    }
    Process
    {
        $connection_string = "User Id=$UserName;Password=$Password;Data Source=$DataSource"
        try{
            $con = New-Object System.Data.OracleClient.OracleConnection($connection_string)
            $con.Open()
            $cmd = $con.CreateCommand()
            $cmd.CommandText = $Statement
            $da = New-Object System.Data.OracleClient.OracleDataAdapter($cmd)
            $dt = New-Object System.Data.DataTable    
            [void]$da.Fill($dt)     
            return $dt
        } catch {
            Write-Error (“Database Exception: {0}`n{1}” -f `
                $con.ConnectionString, $_.Exception.ToString())
        } finally{
            if ($con.State -eq ‘Open’) { $con.close() }
        }
    }
    End
    {
    }
}

Example usage:

Execute-OracleSQL -UserName "myUser" -Password "myPass" -DataSource "myDatasource" -Statement "Select 'Hello world! from dual'

There are better ways to store your scripts as modules but this is the easiest way to load the new function for all your powershell sessions.