Home » RDBMS Server » Networking and Gateways » ORA-02047: cannot join the distributed transaction in progress (ORACLE 11g R2 on linux)
ORA-02047: cannot join the distributed transaction in progress [message #578812] Tue, 05 March 2013 07:15 Go to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Hellow All,

I am trying to create dblink from oracle11GR2@linux to sql using easysoft odbc driver.

DBlink is successfull, i am able to use insert statements in SQL.

I want to insert in SQL database table whenever their is a insert in Oracle Table,I have wrote a triger for this,but i am having an issues on inserting in Oracle Table.I wrote the trigger as below.

create or replace
TRIGGER trig_tbluser1
after INSERT OR UPDATE OR DELETE ON TBLUSERPREFERENCE1
FOR each ROW
BEGIN
  IF     inserting THEN 
    INSERT INTO tbluser@hslink
        
        VALUES
        (
        :NEW.userid,:NEW.password,:NEW.username
        );
       
  ELSIF  updating  THEN NULL;
  ELSIF  deleting  THEN NULL;
END IF;
END;


Now when i am executing insert statement, i am getting the below error,

SQL> Insert into TBLUSERPREFERENCE1 (USERID,PASSWORD,USERNAME) values ('123','abcd','bceef');
Insert into TBLUSERPREFERENCE1 (USERID,PASSWORD,USERNAME) values ('123','abcd','bceef')
            *
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "IRIS.TRIG_TBLUSER1", line 3
ORA-04088: error during execution of trigger 'IRIS.TRIG_TBLUSER1'


Can anyone assist me solving the issue. Your urgent help is required.
Re: ORA-02047: cannot join the distributed transaction in progress [message #578816 is a reply to message #578812] Tue, 05 March 2013 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-02047: cannot join the distributed transaction in progress
 *Cause: Either a transaction is in progress against a remote database
         that does not fully support two phase commit, and an update
         is attempted on another database, or updates are pending and
         and an attempt is made to update a different database that
         does not fully support two phase commit.
 *Action: complete the current transaction and then resubmit the
          update request.

Regards
Michel
Re: ORA-02047: cannot join the distributed transaction in progress [message #578817 is a reply to message #578816] Tue, 05 March 2013 07:45 Go to previous messageGo to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Thank Michel, I have already checked it, now am trying to resolve this issue.
Re: ORA-02047: cannot join the distributed transaction in progress [message #578818 is a reply to message #578817] Tue, 05 March 2013 07:59 Go to previous messageGo to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
OK Guys i have resolved the issue but their is a drawback of this solution... can anyone help me with this... I have modified my triger and now its working fine... but if i rollback in oracle it doesnot rollback in SQL. Please check below.

create or replace
TRIGGER trig_tbluser1
after INSERT OR UPDATE OR DELETE ON TBLUSERPREFERENCE1
FOR each ROW
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF     inserting THEN 
    INSERT INTO tbluser@hslink
        
        VALUES
        (
        :NEW.userid,:NEW.password,:NEW.username
        );
       
  ELSIF  updating  THEN NULL;
  ELSIF  deleting  THEN NULL;
END IF;
commit;
END;
Re: ORA-02047: cannot join the distributed transaction in progress [message #578819 is a reply to message #578818] Tue, 05 March 2013 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are correct in your assumption.
The problem is that your remote database does not support 2 phase commit protocol with Oracle.
So you cannot modify in the same transaction in both databases.

In addition, as a side note, if you do nothing on UPDATE and DELETE then it is useless to put these events in the trigger definition.

Regards
Michel

[Updated on: Tue, 05 March 2013 08:19]

Report message to a moderator

Re: ORA-02047: cannot join the distributed transaction in progress [message #578820 is a reply to message #578819] Tue, 05 March 2013 08:34 Go to previous messageGo to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Thank Bro..... Is there anyway to cater this ? my remote database is ms sql server.
Re: ORA-02047: cannot join the distributed transaction in progress [message #578828 is a reply to message #578820] Tue, 05 March 2013 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know any but a simple search on Google give me that:
http://www.google.com/#hl=en&sclient=psy-ab&q=oracle+sql+server+2+phase+commit

And the second link has a section about Configuring Two-Phase Commit with SQL Server.

Regards
Michel
Re: ORA-02047: cannot join the distributed transaction in progress [message #578862 is a reply to message #578828] Tue, 05 March 2013 23:13 Go to previous messageGo to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Thanks Michel, This helped me out with solving the issue.
Re: ORA-02047: cannot join the distributed transaction in progress [message #580725 is a reply to message #578862] Wed, 27 March 2013 09:30 Go to previous message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Hello All,

I contacted easysoft for two way commit, their driver doesnot support two commit. They said you can use it as below code.


DECLARE c NUMBER; n NUMBER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@odbc;
  DBMS_HS_PASSTHROUGH.PARSE@odbc(c,'insert into insert_performance_test values
    ( 1, ''This is data insert test 1''),
    ( 2, ''This is data insert test 2''),
    ( 3, ''This is data insert test 3'')');
  n := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@odbc (c);
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@odbc(c);
END;
/



I am not able to achieve my required output using the given example... Anyone can guide me plz...

I looking for solution without using PRAGMA AUTONOMOUS_TRANSACTION.
Previous Topic: Client and database Server on same machine
Next Topic: ORA-12537 TNS connection closed
Goto Forum:
  


Current Time: Thu Mar 28 18:58:44 CDT 2024