Avatar of Vidhyalakshmi Sugumar
Vidhyalakshmi Sugumar
 asked on

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

DatabasesOracle Database.NET ProgrammingPowershellSQL

Avatar of undefined
Last Comment
Vidhyalakshmi Sugumar

8/22/2022 - Mon
Mark Geerlings

The "distributed transaction" error indicates that you have two separate Oracle database instances involved.  In this scenario, certain SQL and/or PL\SQL commands are not allowed.  One of these is the "SET TRANSACTION" command that is apparently in the "REFRESH" procedure of DBMS_SNAPSHOT.

It looks like this is a system design issue.  If you have two separate Oracle database instances, you have to limit yourself to those SQL and/or PL\SQL commands that are allowed in this environment
Vidhyalakshmi Sugumar

ASKER
The Package Procedure exists for a long time now.So aim being asked to handle it from Powershell.
ASKER CERTIFIED SOLUTION
Vidhyalakshmi Sugumar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Vidhyalakshmi Sugumar

ASKER
I found the rollbacksegment to cause that error,so checked with DBA and learnt that parameter no longer stands valid.
Your help has saved me hundreds of hours of internet surfing.
fblack61