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:16] (aktuell) – [Auslesen] gpipperr
@@ Zeile 85: / Zeile 85: @@
 Variante 12c R2 SQL*Plus mit der „set markup csv“ Formatierung:
-<code powershell>
+<code powershell getData.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
+		read data to the oracle database with sqlplus
 	.DESCRIPTION
-		write data to the oracle database with sqlplus
+		read data to the oracle database with sqlplus
 	.COMPONENT
 		Oracle PS Helper Scripts
@@ Zeile 107: / Zeile 109: @@
 #==============================================================================
 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="C:\s-akaby\userPWDImport.csv"
+$data_csv="$scriptpath\userPWDImport.csv"
@@ Zeile 129: / Zeile 141: @@
 foreach( $line in $input_csv )
 {
+	#debug
+	write-host $line
-write-host $line
+	$line="'$line'"
+	write-host $line
-$line="'$line'"
+# Write  the data with sqlplus	12c R2! (set markup csv on quote off!)
-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
@@ Zeile 143: / Zeile 156: @@
 set markup csv on quote off
 set heading off
-select $line as val1, sysdate as val2 from dual;
+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()
+	$writeData=$writeData.trim()
-#DEBUG
+	#DEBUG
-write-host $writeData
+	write-host $writeData
 }
 #write to file
-#$readData | Out-File -FilePath "$data_csv" -Append
+$readData | Out-File -FilePath "$data_csv" -append
@@ Zeile 160: / Zeile 174: @@
 </code>
 Das Password sollte natürlich hier nicht in Klarschrift in einem produktiven Skript stehen!
-===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 207: @@
 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 242: @@
 {
-#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 272: @@
 "@| & "$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 ====================================