The purpose of this example is to demonstrate two ways to process Oracle data with a SQL query. First is by using the DataReader (streaming) and the second is by using the DataAdapter (fills an object in memory).

Then as a bonus, we convert that data to HTML and email it.
For an example of how to format the email with pretty bordered-tables and colors, please see Powershell Monitoring and Email of EventLog errors/

In the real world, you will use one method or the other, i.e. DataReader or DataAdapter. In this example, there was built-in functionality to turn the DataTable filled in by the DataAdapter into an HTML table.

The SQL that I crafted just creates two rows with three columns, an doesn’t access any real tables. But it does rely on an Oracle Server to process this SQL. So to run this code, you will have to have security and be able to connect to an Oracle Server, but you won’t need any special tables.

<pre>Add-Type -Path "c:\Program Files\PackageManagement\NuGet\Packages\Oracle.ManagedDataAccess.Core.2.18.6\lib\netstandard2.0\Oracle.ManagedDataAccess.dll"
cls 
$username = "xxxxx"
$password = "xxxxx"
#$datasource = Read-Host -Prompt "Enter database TNS name"
$datasource = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Dev.YourServer.com)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=YourService)))"

$query = @"
   select 'A1' as col1, 'B1' as col2, 'C1' as col3 from dual 
     union 
   select 'A2' as col1, 'B2' as col2, 'C2' as col3 from dual 
"@  # must be in column 1 

$connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource
write-host $connectionString 
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
$connection.open()

$command=$connection.CreateCommand()
$command.CommandText=$query
$reader=$command.ExecuteReader()
$rowNum = 0 
while ($reader.Read()) 
{
   $rowNum = $rowNum + 1 
   Write-Host "Row:$rowNum $($reader["Col1"])  $($reader["Col2"])  $($reader["Col3"]) "
}

Write-Host "`nTry Data Adapter " 
$dataAdapter = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter 
$dataAdapter.SelectCommand = New-Object Oracle.ManagedDataAccess.Client.OracleCommand ($query, $connectionString) 

$ds = New-Object System.Data.DataSet 
$dt = New-Object System.Data.DataTable 
Write-Host "Fill" 
$dataAdapter.fill($ds) 
$connection.Close()

# Convert to HTML 
$HTMLmessage = $ds.Tables[0] |select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html  #same line 

$HTMLMessage = [string] $HTMLMessage 

Write-Host  "HTML:"
Write-Host  $HTMLmessage 

$fromEmail = "nwalters@YourServer.com"
$toEmail = "nwalters@YourServer.com"
$emailSubject = "Demo Email from Powershell"
$smtpServer = "smtp.YourServer.com"
send-mailmessage -from $fromEmail -to $toEmail -subject $emailSubject  -BodyAsHTML -body $HTMLmessage -priority High -smtpServer $smtpServer

</pre>

The sample email viewed in Outlook is shown below. As mentioned int the first paragraph, it is possible to format the table more nicely. This is done with a cascading style sheet (CSS). Please see Powershell Email with CSS (from EventLog)/

Note, before writing the above script, I had to make sure the necessary components were installed. The following should do that:

<pre>Register-PackageSource -Name nuget.org -Location https://www.nuget.org/api/v2 -ProviderName NuGet
Install-Package Oracle.ManagedDataAccess.Core
</pre>