Hi,

I have a piece of sql code which I am running from powershell. The output is the count. If the count is greater than 10, I need to execute a command. Is there a way, I can modify my script so I can check if count is greater than 10.

Any Help will be greatly appreciated.

COUNT(*)                                                                     

----------                                                                     

         0  

sqlplus username/password@TnsAlias 'c:\path\to\DBscript.sql' | out-file 'c:\temp\sql-output.txt'

DBscript.sql
select count(*)
from pa_lane_txn a

Hi there,

could you just write you count statement here and i will help you with the script needed ?

Please tell us the exact output of the following command:

Hi Again,

use below script it is much easy than using SQLPlus

I have tested the script as you see

[string]$query = "select count(*) from pa_lane_txn a"
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | out-null
$connection = new-object system.data.oracleclient.oracleconnection( `
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your Oracle server IP)(PORT=1521)) `
(CONNECT_DATA=(SERVICE_NAME=Service Name)));User Id=Username;Password=Password;");
$set = new-object system.data.dataset
$adapter = new-object system.data.oracleclient.oracledataadapter ($query, $connection)
$adapter.Fill($set)
$table = new-object system.data.datatable
$table = $set.Tables[0]
$num = $table | select "COUNT(*)"
if($num.'COUNT(*)'.ToString() -gt "10"){Write-Host "Greater than 10" }
else{Write-Host "Not greater than 10" }

Hi Again,

use below script it is much easy than using SQLPlus

I have tested the script as you see

[string]$query = "select count(*) from pa_lane_txn a"
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | out-null
$connection = new-object system.data.oracleclient.oracleconnection( `
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your Oracle server IP)(PORT=1521)) `
(CONNECT_DATA=(SERVICE_NAME=Service Name)));User Id=Username;Password=Password;");
$set = new-object system.data.dataset
$adapter = new-object system.data.oracleclient.oracledataadapter ($query, $connection)
$adapter.Fill($set)
$table = new-object system.data.datatable
$table = $set.Tables[0]
$num = $table | select "COUNT(*)"
if($num.'COUNT(*)'.ToString() -gt "10"){Write-Host "Greater than 10" }
else{Write-Host "Not greater than 10" }

• Marked as answer by PowerShellNovice 14 hours 28 minutes ago

Hi Again,

use below script it is much easy than using SQLPlus

I have tested the script as you see

[string]$query = "select count(*) from pa_lane_txn a"
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | out-null
$connection = new-object system.data.oracleclient.oracleconnection( `
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your Oracle server IP)(PORT=1521)) `
(CONNECT_DATA=(SERVICE_NAME=Service Name)));User Id=Username;Password=Password;");
$set = new-object system.data.dataset
$adapter = new-object system.data.oracleclient.oracledataadapter ($query, $connection)
$adapter.Fill($set)
$table = new-object system.data.datatable
$table = $set.Tables[0]
$num = $table | select "COUNT(*)"
if($num.'COUNT(*)'.ToString() -gt "10"){Write-Host "Greater than 10" }
else{Write-Host "Not greater than 10" }

• Marked as answer by PowerShellNovice Tuesday, July 07, 2015 4:40 PM

$text=sqlplus username/password@TnsAlias 'c:\path\to\DBscript.sql'

if($text -match 'count (?<count>\d+)'){$count=[int32]$matches['count']}else{$count=0}