| Using SQL Server resultset in Oracle procedure [message #328594] |
Fri, 20 June 2008 10:48  |
white_eagle
Messages: 3
Registered: June 2008
|
Junior Member |
|
|
I get the error message-'ORA-02047: cannot join the distributed transaction in progress' when trying to update an Oracle database table while looping through data returned by a SQL Server procedure.
My inisql.ora file includes the following lines:
HS_FDS_IS_FUNC=TRUE
HS_FDS_RESULT_SUPPORT=TRUE
HS_FDS_TRANSACTION_MODEL=READ_ONLY
My Oracle procedure includes the following, and fails at the piont where attempting to insert into the Oracle table PTL_Test
curMSQL SYS_REFCURSOR;
rec PTL_TASKLIST_IMPORT%rowtype;
sEmail varchar(100) := 'testname@test.com';
BEGIN
"dbo"."spCHPGetPortalData"@BETH.AMS(sEmail, 4, curMSQL);
for i in 1 .. 1
LOOP
FETCH curMSQL INTO rec;
EXIT WHEN curMSQL%NOTFOUND;
INSERT INTO PTL_Test
SELECT 4 FROM DUAL;
END LOOP;
COMMIT;
CLOSE curMSQL;
COMMIT;
Eventually I want to update an Oracle table from the data retrieved from the SQL Server procedure.
As far as I can see I have everything set up correctly, so what could be the cause of the error?
The DB Link works and if I just display the data retrieved from the Stored proc without attempting to update the Oracle table, everything is OK. so why does the error occur?
Incidentally when I examine v$lock, I have no locks in place.
|
|
|
|
| Re: Using SQL Server resultset in Oracle procedure [message #328595 is a reply to message #328594] |
Fri, 20 June 2008 10:54  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
02047, 00000, "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.
>As far as I can see I have everything set up correctly
Obviously Oracle disagrees with you.
1) I suggest eliminating the COMMIT from inside the cursor loop.
2) INSERT INTO PTL_Test values(4) -- is more efficient
[Updated on: Fri, 20 June 2008 10:57] by Moderator Report message to a moderator |
|
|
|
| Re: Using SQL Server resultset in Oracle procedure [message #328596 is a reply to message #328595] |
Fri, 20 June 2008 11:01 |
white_eagle
Messages: 3
Registered: June 2008
|
Junior Member |
|
|
Yes, I have read the manual, but this does not explain why the error is occurring in my situation.
There is no distributed transaction in progress as far as I can see, the linked SQL Server is read only. SO I have executed the stored procedure on the SQL Server, read the first record from the returned resultset and at the only attempt to update a table the error occurs.
|
|
|
|
|
|
|
|
| Re: Using SQL Server resultset in Oracle procedure [message #329775 is a reply to message #329676] |
Thu, 26 June 2008 08:40 |
|
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
| white_eagle wrote on Thu, 26 June 2008 01:40 |
The solution was to remove the HS_FDS_TRANSACTION_MODEL=READ_ONLY
from the inisql.ora file.
|
As far as I know, neither (HS_FDS_TRANSACTION_MODEL or inisql.ora) is from generic, out of the box RDBMS Oracle.
So how did they get onto your system & in your way?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
