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.

Posted on 2014-11-19
5
797 Views
Last Modified: 2014-12-06
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

0
Comment
Question by:Vidhyalakshmi Sugumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40455714
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
0
 

Author Comment

by:Vidhyalakshmi Sugumar
ID: 40463139
The Package Procedure exists for a long time now.So aim being asked to handle it from Powershell.
0
 

Accepted Solution

by:
Vidhyalakshmi Sugumar earned 0 total points
ID: 40475053
DBMS_SNAPSHOT.REFRESH(list => vName, method => vMethod);
0
 

Author Closing Comment

by:Vidhyalakshmi Sugumar
ID: 40484425
I found the rollbacksegment to cause that error,so checked with DBA and learnt that parameter no longer stands valid.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question