Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- windows:powershell_sqlplus_db_abfragen [2017/12/01 16:58] – [Einlesen] gpipperr
+++ windows:powershell_sqlplus_db_abfragen [2017/12/01 17:11] – [Auslesen] gpipperr
@@ Zeile 85: / Zeile 85: @@
 Variante 12c R2 SQL*Plus mit der „set markup csv“ Formatierung:
-<code powershell>
+<code powershell getData.ps1>
 #==============================================================================
@@ Zeile 163: / Zeile 164: @@
-===Einlesen===
+===Einlesen der CSV Daten===
+SQL Beispiel mit einem Update
 <code powershell updateData.ps1 >
 #==============================================================================
-# Desc:   write data to the oracle database with sqlplus
+# Author: Gunther Pippèrr ( http://www.pipperr.de )
-# Date:   01.12.2017
+# Desc:   Library for the Oracle Data import / Export  scripts
+# Date:   01.Dezember 2017
+# Site:   http://orapowershell.codeplex.com
 #==============================================================================
 <#
  	.NOTES
-		Created: 01.2017 :
+		Created: 11.2017 :
 	.SYNOPSIS
 		write data to the oracle database with sqlplus
@@ Zeile 187: / Zeile 193: @@
 Set-Variable CONFIG_VERSION "0.2" -option constant
-# Set environment
+# 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="d:\temp\userDataImport.csv"
+$data_csv="$scriptpath\userDataImport.csv"
-#set the Oracle HOME variable
+# 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
+   new-item -path env: -name ORACLE_HOME -value $oracle_home
 }
+#==============================================================================
 #read the data input
 $input_csv = Get-Content "$data_csv"
@@ Zeile 209: / Zeile 228: @@
 {
-#debug
+	#debug
-#write-host $line
+	#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
+	# split the csv
-$val1,$val2,$val3 = $line.split(' ')
+	#
+	$val1,$val2,$val3 = $line.split('|')
-#debug
+	#debug
-$val1="'$val1'"
+	write-host $val1
-write-host $val1
+	write-host $val2
-$val2="'$val2'"
+	write-host $val3
-write-host $val2
-$val3="'$val3'"
-write-host $val3
-$command="select $val1 as val1, $val2 as val2, $val3 as val3 from dual;"
+	$command="update dataTab set val1='$val1' , val2='$val2' where val3='$val3'"
-#Write  the data with sqlplus
+# Write  the data with sqlplus
+# must be on start of line!!
 $writeData=@"
 set pagesize 0
@@ Zeile 234: / Zeile 258: @@
 "@| & "$env:ORACLE_HOME\sqlplus" -s "$sql_connect_string"
-try {
+	# trim the response
-  $writeData=$writeData.trim()
+	try {
-}
+	  $writeData=$writeData.trim()
-catch {
+	}
-   write-host "Object is empty - check your code"
+	catch {
+	   write-host "Object is empty - check your code"
+	}
+	#DEBUG
+	write-host $writeData
 }
-#DEBUG
+$stoptime=get-date
-write-host $writeData
+write-host "Info -- finsh the Script at $stoptime"  -ForegroundColor "green"
-}
 #============================= End of File ====================================