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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Flush end users Deleted Items via PowerShell 2 27
error in oracle form 11 26
SQL syntax question 6 41
Oracle programming for starter 14 32
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

749 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