Solved: Could not execute oracle stored procedure from powershell using ODP.net,ORA-02074: cannot SET TRANSACTION in a distributed transaction. DBMS_SNAPSHOT.REFRESH is causing trouble,I need a workaround.

if ($ora_loaded -eq $null)
{
    [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess") 
    [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
}


function New-Connection
{
    $connectionString = "User Id=XYZ;Password=ABC;Data Source=dbNAME" 
    New-Object Oracle.DataAccess.Client.OracleConnection($connectionString)
}

function New-ProcCommand ($procedure, $parameters)
{

    $cmd = New-Object Oracle.DataAccess.Client.OracleCommand($procedure, (New-Connection))
    $cmd.CommandType = [Oracle.Data.CommandType]::StoredProcedure
    $parameters | foreach {$cmd.Parameters.Add($_)| Out-Null} 
    $cmd
}


function New-Param ($name, $type, $value,$size = 0, $direction = [System.Data.ParameterDirection]::Input)
{
    New-Object Oracle.DataAccess.Client.OracleParameter($name,$type, $size) -property @{Direction = $direction; Value = $value}
}
function New-Param ($name, $type, $value,$size = 0, $direction = [System.Data.ParameterDirection]::Input)
{
    New-Object Oracle.DataAccess.Client.OracleParameter($name,$type, $size) -property @{Direction = $direction; Value = $value}
}


function Invoke-Proc ($procedure, $parameters)
{
$cmd = New-ProcCommand $procedure $parameters
if ($cmd.Connnection.State -ne [System.Data.ConnectionState]::Open) {$cmd.Connection.Open()}
$cmd.ExecuteNonQuery() #| out-null
$cmd.Connection.Close()
}

function REFRESH_MV {
	[CmdletBinding()] # Identfies this as an Advanced Function
	param(
		[Parameter(Mandatory=$true,ValueFromPipeline=$True)]
		[ValidateNotNullorEmpty()]
		[string]$vName)
    $procedure = "PROC_REFRESH_MV"
    $params = @((New-Param -name "vName" -type ([Oracle.DataAccess.Client.OracleDbType]::Varchar2) -value $vName)
    (New-Param -name "vMethod" -type ([Oracle.DataAccess.Client.OracleDbType]::Varchar2) -value "C")
    (New-Param -name "vRollbackSegment" -type ([Oracle.DataAccess.Client.OracleDbType]::Varchar2) -value null)
    (New-Param -name "nRetries" -type ([Oracle.DataAccess.Client.OracleDbType]::Int32) -value 1))
    Invoke-Proc -procedure $procedure -parameters $params
}

REFRESH_MV  -vName "EMPLOYEE_DETAILS_MV"

Open in new window

--Oracle piece
CREATE OR REPLACE PROCEDURE PROC_REFRESH_MV
(vName IN VARCHAR2, vMethod IN VARCHAR2, vRollbackSegment IN VARCHAR2,
nRetries NUMBER)
IS
BEGIN

DBMS_SNAPSHOT.REFRESH(list => vName, method => vMethod, rollback_seg => vRollbackSegment);

END PROC_REFRESH_MV;

Open in new window