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.
--Oracle piece
CREATE OR REPLACE PROCEDURE PROC_REFRESH_MV
(vName IN VARCHAR2, vMethod IN VARCHAR2, vRollbackSegment IN VARCHAR2,
nRetries NUMBER)
IS
BEGIN
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.
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