#==============================================================================
# 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
		write data to the oracle database with sqlplus
	.DESCRIPTION
		write data to the oracle database with sqlplus
	.COMPONENT
		Oracle PS Helper Scripts
#>

#==============================================================================
# Environment
#==============================================================================
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

# My local Settings

$oracle_home="C:\oracle\products\12.2.0.1\client_64"
$sql_connect_string="gpi/gpi@gpidb"
$data_csv="$scriptpath\userDataImport.csv"


# set the 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 the data input
$input_csv = Get-Content "$data_csv"

# loop over each line of the data input

foreach( $line in $input_csv )
{

	#debug
	#write-host $line 
	
	#check for empty lines and skip this lines
	for ($line.length() -le 5) { 
	    # exit from this iteration round
		continue
	}

	# split the csv
	# 
	$val1,$val2,$val3 = $line.split('|')

	#debug
	write-host $val1
	write-host $val2
	write-host $val3

	$command="update dataTab set val1='$val1' , val2='$val2' where val3='$val3'"

# Write  the data with sqlplus	
# must be on start of line!!
$writeData=@"
set pagesize 0 
set feedback off
$command
commit;
quit
"@| & "$env:ORACLE_HOME\sqlplus" -s "$sql_connect_string"

	# trim the response
	try {
	  $writeData=$writeData.trim()
	}
	catch {
	   write-host "Object is empty - check your code"
	}

	#DEBUG
	write-host $writeData

}

$stoptime=get-date
write-host "Info -- finsh the Script at $stoptime"  -ForegroundColor "green"	


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