#
# Strategies 
#
# Liste des sessions Oracle
# Oracle Data Provider for .NET
$OracleDllPath="$PSScriptRoot\Oracle.ManagedDataAccess.dll"
if (Test-Path -Path $OracleDllPath) {
    Add-Type -Path $OracleDllPath
} else {
    Write-Host "ERROR - Missing $OracleDllPath"
    Start-Sleep -Seconds 4
    return
}


function Invoke-OracleSQLSelect {
   [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,
       [scriptblock]
       $scriptBlockToCall = ${function:displayFirstValue},

       [string[]]
       $scriptBlockParams = ("", ""),

       [bool]
       $asSysDba = $false
   )

   Begin {
   }
   Process {

       $connection_string = getConnectionString -username $UserName -password $Password -asSysDba $asSysDba
       try {
           $con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connection_string)
           $con.Open()
           $cmd = $con.CreateCommand()
           $cmd.CommandText = $Statement
           $rdr = $cmd.ExecuteReader()
           while ($rdr.read() -ne $false) {
               Invoke-Command $scriptBlockToCall -ArgumentList $rdr, $scriptBlockParams[0], $scriptBlockParams[1], $scriptBlockParams[2], $scriptBlockParams[3]
           }
       }
       catch {
           Write-Error($_.Exception.ToString())
       }
       finally {
           if ($con.State -eq "Open") { $con.close() }
       }
   }
   End {
   }
}

function displayConnections {
   param ([System.Object] $rdr)
   $local:msg = $([string]::Format("{0}{1}{2}{3}{4}{5}", $rdr.getString(0).Padright(15), $rdr.getString(1).PadRight(15), $rdr.getString(2).PadRight(15), $rdr.getString(5).PadRight(10), $rdr.getString(3).PadRight(30), $rdr.getString(4)))
   write-host $local:msg
}

function getConnectionString {
   param([string] $username, [string] $password, [bool] $asSysDba = $false)
   $connection_string = "User Id=$username;"
   $connection_string += "Password=$password;"

   if ($asSysDba -eq $true) {
       $connection_string += "DBA Privilege=sysdba;"
   }

   $connection_string += "Data Source=$DataSource;"
   $connection_string += "Pooling=False;"

   return $connection_string

}

function getOCIDescriptor {
   param ([string] $hostname, [string] $port = "1521", [string] $service, [string] $sid)
   $local:connect_data = "";
   if ($PSBoundParameters.ContainsKey('service')) {
       $local:connect_data = "SERVICE_NAME=$service"
   }
   elseif ($PSBoundParameters.ContainsKey('sid')) {
       $local:connect_data = "SID=$sid"
   }
   return "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$hostname)(PORT=$port))(CONNECT_DATA=($local:connect_data)))"
}


$descriptor = getOCIDescriptor -hostname "ol8-19-14-a.dmz.cadwin.com" -service "dev_vl.dmz.cadwin.com"
Invoke-OracleSQLSelect -UserName "SYSTEM" -Password "SYSTEM" -DataSource $descriptor -Statement 'select username, osuser, machine, program, coalesce(module, '', '') as module, status, status from v$session where audsid > 0 and (sys_context(''USERENV'', ''SESSIONID'') != audsid)' -scriptBlockToCall ${function:displayConnections}