a simple powershell script for calling an oracle stored procedure with both input and output parameters

test.sql.ps1

$asm = [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") 
			
$connectionString = "Data Source=myDatabase;uid=sa;pwd=sa";			
$inputString = "foo";

$oracleConnection = new-object System.Data.OracleClient.OracleConnection($connectionString);
$cmd = new-object System.Data.OracleClient.OracleCommand;
$cmd.Connection = $oracleConnection;

$cmd.CommandText = "isFoo";
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure;

$cmd.Parameters.Add("inputString", [System.Data.OracleClient.OracleType]::VarChar) | out-null;
$cmd.Parameters["inputString"].Direction = [System.Data.ParameterDirection]::Input;
$cmd.Parameters["inputString"].Value = $inputString;
			
$cmd.Parameters.Add("outNumber", [System.Data.OracleClient.OracleType]::Number)  | out-null;	
$cmd.Parameters["outNumber"].Direction = [System.Data.ParameterDirection]::Output;
		
$oracleConnection.Open();
$cmd.ExecuteNonQuery() | out-null;
$oracleConnection.Close();

$result = $cmd.Parameters["outNumber"].Value;
write-host "result of isFoo($inputString) is $result" -foregroundcolor yellow;

Thanks for this... I had the same issue :)

I also had to add:

$cmd.Parameters["outText"].size = 5000;

As I changed the out number to a varchar...