Hello all
In general, I find that Oracle Managed Provider works fine with Powershell. However I have trouble getting binds-vaiables to work. My example is an update. Any hints regarding what I do wrong (example below) is very welcome.
The workflow column is of datatype number in the table. The script runs fine and says that it has changed 1 row. The database column changes to null, no matter what I specify in the bind-variable.
Whether I specify [System.Data.DbType]::Int64 or [System.Data.DbType]::Decimal as bind-type changes nothing.
What do I do wrong?
Add-Type -Path "C:\Oracle12\Oracle12102x64\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll"
try {
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("User Id=/;Data Source=db_dst")
$con.open()
$cmd=$con.CreateCommand()
$cmd.CommandText = "update workflow_tasks_t2 wt set wt.workflow_status = :sysid where wt.sys_id = 1"
$sysid = New-Object Oracle.ManagedDataAccess.Client.OracleParameter
$sysid.Direction = [System.Data.ParameterDirection]::Output
$sysid.DbType = [System.Data.DbType]::Int64
$sysid.Value = [Int64]1
$cmd.Parameters.Add($sysid) | Out-Null
$rows_changed = $cmd.ExecuteNonQuery()
Write-Host "Rows changed:" $rows_changed
}
catch
{
Write-Error ("Error : {0}`n{1}" -f $con.ConnectionString, $_.Exception.ToString())
}
finally
{
if ($con.State -eq 'Open') { $con.close() }
}