Powershell - Retrieve Count from SQLPlus query

Question

I'm working with a powershell script which utilizes SQLPlus to query an oracle database.

The Query is: select count(*) from table

The powershell query outputs:

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 6 09:50:52 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


  COUNT(*)
----------
    50

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

I'm currently using the statement below to retrieve the Count, though it seems to not be capturing the result.

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

Any help/guidance would be much appreciated. Thanks!

Answer 1

I see one problem and a potential issue. The latter being I suspect $file is a string array. That works against you since you are looking for a number based on the presence of text a couple lines above.

The problem is your regex is not multi-line and in your sample the number does not appear on the same line as count. Your regex would only match Count 50. That is not how it appear in the text you show.

Two ensure both points lets make $file one single string and then run a multi-line regex that matches the first number encountered after "COUNT". Keep the named match logic you have as it is nice to have.

# This can most likely be moved to where this is read in.
$file = $file | Out-String

# try and match the regex. 
if($file -match "(?s)Count.*?(?<count>\d+)"){
    [int32]$Matches.Count
}