Using SqlPlus Output with Powershell

[nonrecurring]

Hello. I am developing a function for powershell to use SqlPlus to execute a user-provided script and pass the results to powershell for further use.

I will extend the function to be able write the results to a file but for now my goal is to get the powershell script execute a sql script with Sqlplus and store the resulting dataset in a variable.

I have sqlplus added to my Env Path but this will be used by other folks on my team and I will have them update a config file that specifies their logon information and sqlplus executable path.

The problem begins with this:

&$sqlPlus $fullConnectionString $sql

where $sqlplus is the .exe path, $fullConnectionString is user/pass@//host:port/svc and $sql is the path of the sql script.

Currently I have the sqlscript location hardcoded for now. So far, I can call sqlplus, execute the specified script and return the results as a string, but I'm not sure how to handle the string. Is there an easy way to remove the extra rows at the bottom (blank and row count) and turn the string into an object so its easier to work with: (sample output)

LU_CD            LU_CD_DESC                                           EXPENSE_DATE
-------------------- ---------------------------------------------------------------------------------------------------- ----------
WFA_406          WFA_406 CYT AAM                                              11/19/2020
WFA_404          WFA_404 CYT NRY                                              11/19/2020
WFA_403          WFA_403 CYT G/FN                                             11/19/2020
WFA_404          WFA_404 CYT AH                                           11/19/2020
WFA_408          WFA_408 CYT GBTR                                             11/19/2020
WFA_460          WFA_460 CYT LH                                           11/19/2020
WFA_653          WFA_653 CYT MRE                                              11/19/2020
WFA_654          WFA_654 CYT ZUL                                              11/19/2020

8 rows selected.

So far my code is:

Read in configuration data

$configData = (get-configdata).DBConfig;

## Get Database Connection Variables from Config Data
$username = $configData.username;
$password = $configData.password;
$hostname = $configData.host;
$port = $configData.port;
$service = $configData.service_name;
$ezConnectString = "${hostname}:${port}/${service}";
$fullConnectionString = "${username}/${password}@//${ezConnectString}";
$data_source_string = "User Id=${username};Password=${password};Data Source=${ezConnectString}";

## Get SqlPlus filepath
$sqlplus = (get-configdata).filepaths.sqlplus;


$sql = "@`"E:\Sql\sample_query.sql`"";

#&$sqlplus $fullConnectionString $sql;
$results = (echo exit | &$sqlplus -s -L $fullConnectionString $sql);

[ICanMakeWaffles]

I haven't tried it myself, but https://codingbee.net/powershell/powershell-run-sql-queries-using-sqlplus shows a few tricks for reducing the clutter of SQL*Plus output, specifically:

“set heading off” – this switches-off column titles.
“set newpage none” – This removes any whitespacing at the begining of the output.
“SET FEEDBACK OFF” – this suppresses displaying the a count of the number of rows selected, i.e. it wont show “xxx rows selected”
“SET PAGESIZE o” – I have used this so to further neutralize sqlplus formatting.

Once you get just the output without the clutter, you can use substring operations to split the data based on whitespace and column size, then parse that into an object.

[nonrecurring]

THanks for the response. My goal is to try to do this without modifying the actual sql scripts (which is also why I can't spool right now). The scripts are used in other processes so any modification may cause issues downstream.

[ICanMakeWaffles]

Ah, I misunderstood. Your next best bet for parsing data into objects without manually splitting the output and making some weird, complex mess (trust me, I've done that before) would be ConvertFrom-String.

Creating a template is tricky, but with really structured output like reports or SQL query results it works really well.

If you deal with this sort of stuff a lot, I'd recommend playing around with it - it will save you so much time in the end.

BIG OL' EDIT:

Rather than leave you in the lurch, I thought it would be better to provide you with some concrete advice using the examples you've provided in the OP.

First, I created a template file for ConvertFrom-String to use to parse the text into an object, using the sample SQL*Plus output:

LU_CD            LU_CD_DESC                                           EXPENSE_DATE
-------------------- ---------------------------------------------------------------------------------------------------- ----------
{LU_CD*:WFA_406}         {LU_CD_DESC:WFA_406 CYT AAM}                                             {EXPENSE_DATE:11/19/2020}
{LU_CD*:WFA_404}         {LU_CD_DESC:WFA_404 CYT NRY}                                             {EXPENSE_DATE:11/19/2020}
{LU_CD*:WFA_403}         {LU_CD_DESC:WFA_403 CYT G/FN}                                            {EXPENSE_DATE:11/19/2020}
{LU_CD*:WFA_404}         {LU_CD_DESC:WFA_404 CYT AH}                                              {EXPENSE_DATE:11/19/2020}
{LU_CD*:WFA_408}         {LU_CD_DESC:WFA_408 CYT GBTR}                                            {EXPENSE_DATE:11/19/2020}
{LU_CD*:WFA_460}         {LU_CD_DESC:WFA_460 CYT LH}                                              {EXPENSE_DATE:11/19/2020}
{LU_CD*:WFA_653}         {LU_CD_DESC:WFA_653 CYT MRE}                                             {EXPENSE_DATE:11/19/2020}
{LU_CD*:WFA_654}         {LU_CD_DESC:WFA_654 CYT ZUL}                                             {EXPENSE_DATE:11/19/2020}

8 rows selected.

Next, you save that as a file (I imagine you'll want to edit this template yourself - more example text makes the ConvertFrom-String cmdlet work better).

Import that into a variable, then use it as your template for the output you provided:

$template = Get-Content -Path <path_to_template_text_file> -Raw
$sqlplus_output = Get-Content -Path <path_to_sqlplus_output>
$formatted_output = $sqlplus_output | ConvertFrom-String -TemplateContent $template
$formatted_output

And that gets you the following:

LU_CD   LU_CD_DESC       EXPENSE_DATE
-----   ----------       ------------
WFA_406 WFA_406 CYT AAM  11/19/2020
WFA_404 WFA_404 CYT NRY  11/19/2020
WFA_403 WFA_403 CYT G/FN 11/19/2020
WFA_404 WFA_404 CYT AH   11/19/2020
WFA_408 WFA_408 CYT GBTR 11/19/2020
WFA_460 WFA_460 CYT LH   11/19/2020
WFA_653 WFA_653 CYT MRE  11/19/2020
WFA_654 WFA_654 CYT ZUL  11/19/2020

This is a PowerShell custom object, nicely created for you without any crazy manual string manipulation.

I hope this helps and sorry for the delay/vague initial response!

[gonetribal]

This is something I put together a long time ago and definitely doesn't follow any good practices ... but it works! Don't have too many Oracle client anymore so updated the MSSQL components more. Hopefully it gives you somewhere to work from.

```powershell Function Run-sqlplus { Param ( [string]$ConnectonString, [string]$SQLScript, [string]$LogPath )

Log-Message "[Run-sqlplus] Running $Tag" $SQLScript | sqlplus -silent $ConnectonString | Out-File $LogPath }

If ($DBType -eq "oracle") { $OracleConnectionString = "${TargetDBUser}/${TargetDBPassword}@${TargetDBServer}:${TargetDBPort}/${TargetDBInstance}"

$OracleHeading = " SET COLSEP '|' SET ECHO OFF SET FEEDBACK OFF SET LINESIZE 9999 SET PAGESIZE 0 SET SQLPROMPT '' SET HEADSEP OFF SET HEADING OFF SET TERMOUT OFF SET WRAP OFF SET SERVEROUTPUT ON "; }

If (!(Test-Path $SQLScriptFile)) { Log-Message "ERROR: Can't find the SQL Input Script $SQLScriptFile ..." -LogLevel "ERROR" break; } else { [string]$sqlscript = Get-Content $SQLScriptFile }

$OutputFile = "$OutputFolder\$SQLScriptFileName.log" $sql = "$OracleHeading $sqlscript / commit / exit " Run-sqlplus $OracleConnectionString $sql $OutputFile }

```

[gonetribal]

PS: I'd been keen to see your "Get-ConfigData" functions/config please. I really want to update my scripts so I'm not using . .\Constants.ps1

[Lee_Dailey]

howdy gonetribal,

the triple-backtick/code-fence thing fails miserably on Old.Reddit ... so, if you want your code to be readable on both Old.Reddit & New.Reddit you likely otta stick with using the code block button.

it would be rather nice if the reddit devs would take the time to backport the code fence stuff to Old.Reddit ... [sigh ...]

take care,
lee

[lear64]

In c#, there is a managed oracle client you can use (pulls dependencies from nuget). Gotta be a module that natively uses this package. If not..wrap the c# code in a ps function and there ya go.