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
849 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

634 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