sql - Pass a Variable from a Powershell Script to SQLPlus

Question

I'm trying to pass a variable from my Powershell Script to SQLPlus.

I've defined my variable as $csvStorage (the file path to the folder "csv_files"):

Powershell Script:

# Set file path to C:xxxxxxxxxxxxxxxxcsv_files
$filePath = Split-Path -Path $directory -Parent
$csvStorage = Join-Path $filePath -ChildPath "csv_files"

I have then passed it as an argument to the SQL script:

Powershell Script:

# Run sql_queries.sql whilst passing C:xxxxxxxxxxxxxxxxcsv_files into the SQL script
$queryTables = "/c sqlplus $dbUsername/$dbPassword@$dbServiceName @$filePath $csvStorage"
&$sqlPlus $queryTables

Then finally, referenced the variable in my SQL using '&1':

SQL Script:

set null null
set heading on
set pagesize 50000
set termout on

spool &1hc_actual_vs_shadow_inv.csv

SELECT *
FROM hc_actual_vs_shadow_inv
/
spool off
/
exit

However, the query is not being executed and no .csv file is outputted. But I can't see what I'm doing wrong. Any assistance would be much appreciated.

Thanks

question from:https://stackoverflow.com/questions/65672032/pass-a-variable-from-a-powershell-script-to-sqlplus

Answer 1

<#
    .SYNOPSIS
     This script executes all sql files from the specified directory and creates separate csv files in a specified directory.
     Author: Dmitry Demin dmitrydemin1973@gmail.com
    .DESCRIPTION
     In the script, the format for displaying the date and decimal separator is configured.
    .PARAMETER username
     Specify the username  for example SCOTT
    .PARAMETER password
     Specify the password  for example TIGER
    .PARAMETER connect_string
     Specify the connect_string(TNS alias)  for connect to database from $ORACLE_HOME/network/admin/tnsnames.ora.  
    .PARAMETER sql_path
     Specify the directory for executing sql scripts.
    .PARAMETER csv_path
     Specify the directory for output csv.
    .PARAMETER log_path
     Specify the log file.
    .EXAMPLE
     This script executes all sql files from the specified directory and creates separate csv files in a specified directory.
    .
un_export_all_tables.ps1 -username SCOTT -password tiger -connect_string ORCL -sql_path C:exportsql  -csv_path C:exportcsv
#>


param(
[string]$username = "scott", 
[string]$password = "tiger",
[string]$connect_string = "192.168.0.166:1521/TEST",
[string]$sql_path="C:upworkpowershell_sqlplus_export_csvsql",
[string]$csv_path="C:upworkpowershell_sqlplus_export_csvcsv",
[string]$log_path="C:upworkpowershell_sqlplus_export_csvlog_file.log"
)
# Column separator for csv file 
$COLSEP=";"
# NLS_NUMERIC_CHARACTERS
$NLS_NUMERIC_CHARACTERS=".,"
$NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"
#[string]$connect_string = "server2003ora10:1521/ORCL"
# Log file 
$full_sql_path=$sql_path
$full_csv_path=$csv_path
$full_log_path=$log_path
#csv file extension
$csv_ext=".csv"
#Set NLS_LANG for session sqlplus 
#"RUSSIAN_CIS.UTF8"
#"RUSSIAN_CIS.CL8MSWIN1251"
#"AMERICAN_AMERICA.UTF8"
#$NLS_LANG="RUSSIAN_CIS.CL8MSWIN1251"
$NLS_LANG="AMERICAN_AMERICA.CL8MSWIN1251"
#$NLS_LANG="AMERICAN_AMERICA.UTF8"

#Set NLS_LANG for session sqlplus 
[Environment]::SetEnvironmentVariable("NLS_LANG",$NLS_LANG , [System.EnvironmentVariableTarget]::PROCESS)
$env_path_NLS=[Environment]::GetEnvironmentVariable("NLS_LANG", [EnvironmentVariableTarget]::PROCESS)

echo "SET session NLS_LANG: $env_path_NLS" | tee-object -Append  -filepath $full_log_path


$SqlQueryExportTable1 = 
@"
set heading off
set termout OFF
SET FEEDBACK OFF
SET TAB OFF
set pause off
set verify off
SET UNDERLINE OFF
set trimspool on
set timing off
set echo off
set numwidth 30
set linesize 10000
set pagesize 0
SET COLSEP '$COLSEP'
ALTER SESSION SET NLS_NUMERIC_CHARACTERS='$NLS_NUMERIC_CHARACTERS';
ALTER SESSION SET NLS_DATE_FORMAT='$NLS_DATE_FORMAT'; 
   
"@


$SqlQueryExportTable2 =
@"
exit
"@


function Check_File
{
     param (
          [string]$pathfile 
     )


try {
$A=Get-Content -Path $pathfile  -ErrorAction Stop
}
catch [System.UnauthorizedAccessException]
{
#Write-Host "File $pathfile  is not accessible."
echo "File $pathfile  is not accessible." | tee-object -Append  -filepath $full_log_path


exit
}

catch [System.Management.Automation.ItemNotFoundException]
{
#Write-Host "File $pathfile  is not found."
echo "File $pathfile  is not found." | tee-object -Append  -filepath $full_log_path
exit
}
catch {
Write-Host "File $pathfile.  Other type of error was found:"
#Write-Host "Exception type is $($_.Exception.GetType().Name)"
   echo "Exception type is $($_.Exception.GetType().Name)" | tee-object -Append  -filepath $full_log_path
exit
}

}

                         
echo "===========================================================================================" | tee-object -Append  -filepath $full_log_path



$date_time_start = Get-Date -Format "yyyy-MM-dd HH:mm:ss"            
$date_time_log = Get-Date -Format "yyyyMMddHHmmss"            

Write-host "Script start time : $date_time_start "
try
{
echo "Script start time :  $date_time_start ">>$full_log_path
}
catch {
Write-Host "Log File $full_log_path.  Other type of error was found:"
Write-Host "Exception type is $($_.Exception.GetType().Name)"
exit
}


#chcp 1251


$files_input = Get-Childitem -File $full_sql_path  

foreach ($file_input in $files_input)
{
   echo  "Found SQL file  $file_input  " | tee-object -Append  -filepath $full_log_path


  $full_sql_path_file=$full_sql_path+$file_input 
  $user_tab= get-content -Path $full_sql_path_file  | out-string



 echo  "Found SQL :  $user_tab " | tee-object -Append  -filepath $full_log_path


$sqlQuery_show_table_all=""

 
$sqlQuery_show_table_all=$SqlQueryExportTable1+  $user_tab+ $SqlQueryExportTable2



$full_csv_path_file=$full_csv_path +  $file_input + "_" + $date_time_log + $csv_ext

echo "-------------------------------------------------------------------------------------------"  | tee-object -Append  -filepath $full_log_path
echo "For SQL file : $full_sql_path_file will be created new csv file: $full_csv_path_file" | tee-object -Append  -filepath $full_log_path


echo  "Script will run for SQL: $user_tab "  | tee-object -Append  -filepath $full_log_path

$sqlOutput_tab = $sqlQuery_show_table_all | sqlplus -s $username/$password@$connect_string
$sqlOutput_count = $sqlOutput_tab.count
 if ($sqlOutput_tab.count -gt 0) 
{
Out-File -filepath $full_csv_path_file -append -inputobject $sqlOutput_tab -encoding default
echo  "Exported rows:  $sqlOutput_count "  | tee-object -Append  -filepath $full_log_path
}
else
{
echo  "No exported rows: 0 row"  | tee-object -Append  -filepath $full_log_path
echo  "$full_csv_path_file file not created "  | tee-object -Append  -filepath $full_log_path
}

echo "-------------------------------------------------------------------------------------------"  | tee-object -Append  -filepath $full_log_path


}