Execute statements using SQL*Plus from PowerShell

November 12, 2023 By Arjen Plakke

<#
   .Synopsis
           Run a script in SQLCL
   .DESCRIPTION
           Running a script with SQLCL
           Output is being saved in a variable
   .PARAMETER sqlscript
   .PARAMETER owner
   .PARAMETER password
   .PARAMETER database
   .EXAMPLE
          run-sql -sqlscript “c:scriptssqlscript.sql” -owner “<dbuser>” -password “<password>” -database “<database>”
   .NOTES
 #>

function run-sql
{
   [CmdletBinding()]
   [Alias()]
   [OutputType([int])]
   param
   (
      [Parameter(Mandatory = $true,
                 ValueFromPipelineByPropertyName = $true)]
       $sqlscript,
       $owner,
       $password,
       $database
    )
    Begin
    {
        ##
    }
    Process
    {
        $connect_string = $owner + / + $password + @ + $database
        if ($owner -eq sys)
        {
           Start-Process -FilePath $sqlcl_exe -ArgumentList $owner/`”$password`”@$database as sysdba, @$sqlscript -Wait
         }
         else
         {
            $myarg = $owner/`”$password`”@$database @$sqlscript
            start-Process -FilePath $sqlcl_exe -ArgumentList $myarg -Wait
         }
     }
     End
     {
     }
}