SQL command to PowerShell

PowerShell Help
1

Hi Guys,
I’ve ran into an issue that I can’t seem to find any answers to so maybe you can help me out:
I have a table in SQL
Column1|Column2|Column3|Column4|Column5

I am importing data from a *.csv file into a SQL table using the following code

	$CsvPath = "C:\Temp\File.csv"
	$Csv = Import-Csv $CsvPath -Delimiter ";"
	
	Add-Type -Path "C:\ProgramData\oracle\odp.net\bin\4\Oracle.DataAccess.dll"
	$ConStr = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=;Password=;Data Source=")
	$CMD = $ConStr.CreateCommand()
	$ConStr.Open()
	foreach ($Item in $Csv)
	{
		$Col1 = $Item.col1
		$Col2 = $Item.col2
		$Col3 = $Item.col3
		$Col4 = $Item.col4
	        $Col5 = $Item.col5
	
		## Insert data in table FOLDER_PERMISSION
		$CMD.CommandText = "INSERT INTO  (Col1 ,Col2 ,Col3 ,Col4 ,Col5 ) VALUES('{0}','{1}','{2}','{3}','{4}')" -f "$Col1", "$Col2", "$Col3 ", "$Col4", "$Col5"
		$CMD.executenonquery()
	}
	$ConStr.close()

This works nicely until I encounter a large string in Column5 and it throws an error, and then again I have found a workaround for this directly in SQL:

cCol5 clob := 
begin

insert into 
  (col1,col2,col3,col4,col5)
values
  ('Test Value',
  'Test Value2',
  'Test Value3',
   cCol5
  );

So this little peace of code works in SQL and I have no clue how to translate this in a SQL powershell statement that would do the same thing from PS.

I have tried the following with no result:

...
$CMD.CommandText = "
cCol5 clob := 
begin

insert into 
  (col1,col2,col3,col4,col5)
values
  ('Test Value',
  'Test Value2',
  'Test Value3',
   cCol5
  );
"
...

Any and all help will be useful, please let me know if further details are required.
Regards,
Sergiu Creifelean
2

This is really a SQL question; there’s no way to do this “in” PowerShell, but you can easily add the statement to your SQL statement string. Just separate statements with a semicolon.

3

If I’m following the logic, when col5 is too big, you store it in col4 instead?

If that’s correct, just test the value’s length and use a different CommandText statement.

I assume there’s a way to query the table definition to read the max size of Col5 so you don’t have to hard code it in your script.

4

In your example, I don’t see you actually setting the value of cCol5. Try using a here-string as it allows you to format the SQL, the only catch is you cannot indent it:

$CsvPath = "C:\Temp\File.csv"
$Csv = Import-Csv $CsvPath -Delimiter ";"
	
Add-Type -Path "C:\ProgramData\oracle\odp.net\bin\4\Oracle.DataAccess.dll"
$ConStr = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=;Password=;Data Source=")
$CMD = $ConStr.CreateCommand()
$ConStr.Open()
foreach ($Item in $Csv)
{

$cmd = @"
    insert into 
      (col1,
       col2,
       col3,
       col4,
       col5 clob )
    values
      ('$($Item.col1)',
       '$($Item.col2)',
       '$($Item.col3)',
       '$($Item.col4)',
       '$($Item.col5)',
      );

"@
	
	## Insert data in table FOLDER_PERMISSION
	$CMD.CommandText = $cmd
	$CMD.executenonquery()
}

$ConStr.close()
5

Hi all,

@Ron, it was a editing mistake, I forgot to add Test Value4, thank you for pointing that out.
@Rob Simmers
Rob the code you added throws an oracle error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "ORA-00917: missing comma"
At C:\Users\L1_RO10025\Documents\SIG-Data\Utils\saveSQL.ps1:28 char:5
+     $CMD.executenonquery()
+     ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OracleException

and it seems that this error is related to col5 clob. Any ideas?

@Don Jones
Don I tried it, but it does nothing, so no errors but no data in the table as well. Any other ideas?

6

Hi all,
this is what works for me:

$SQLIn =  @"
DECLARE
cVariable clob:= 'HUGE STRING';
BEGIN
INSERT INTO TableName(Col1,Col2,Col3,Col4,Col5)
VALUES('$($Col1)','$($Col2)','$($Col3','$($Col4)',cVariable);
END;
"@

Thank you everyone for your time and effort and I hope this will help someone else as well.
Regards,
Sergiu