#==============================================================================
# Author: Gunther Pippèrr ( http://www.pipperr.de )
# Desc:   Library for the Oracle Data import / Export  scripts
# Date:   01.Dezember 2017
# Site:   http://orapowershell.codeplex.com
#==============================================================================

<#
 	.NOTES
		Created: 11.2017 :
	.SYNOPSIS
		read data to the oracle database with sqlplus
	.DESCRIPTION
		read data to the oracle database with sqlplus
	.COMPONENT
		Oracle PS Helper Scripts
#>

#==============================================================================
# Enviroment
#==============================================================================
Set-Variable CONFIG_VERSION "0.2" -option constant

# Rember the script path to find the csv
$Invocation = (Get-Variable MyInvocation -Scope 0).Value
$scriptpath=Split-Path $Invocation.MyCommand.Path

$starttime=get-date

write-host "Info -- start the Script in the path $scriptpath at $starttime"  -ForegroundColor "green"	

cd  $scriptpath # Set working directory if executed as job

# Set enviroment
$oracle_home="C:\oracle\instantclient_12_2"
$sql_connect_string="gpi/gpi@gpidb"
$data_csv="$scriptpath\userPWDImport.csv"


#set Oracle HOME variable
try {
	set-item -path env:ORACLE_HOME -value $oracle_home
}
catch {
	new-item -path env: -name ORACLE_HOME -value $oracle_home
}
	
#read input
$input_csv = Get-Content "$data_csv"

# loop over each line

foreach( $line in $input_csv )
{
	#debug
	write-host $line 


	$line="'$line'"
	write-host $line

# Write  the data with sqlplus	12c R2! (set markup csv on quote off!)
# for all other use the older format settings!
# must be on start of line!!
$writeData=@"
set pagesize 0 
set feedback off
set markup csv on quote off
set heading off
select $line as val1, sysdate as val2,sysdate+1 as val3 from dual;
quit
"@| & "$env:ORACLE_HOME\sqlplus" -s "$sql_connect_string"

	$writeData=$writeData.trim()

	#DEBUG
	write-host $writeData

}

#write to file
$readData | Out-File -FilePath "$data_csv" -append


#============================= End of File ====================================