When using with DBLink: ORA-01453: SET TRANSACTION must be first statement of transaction

From: <schreurs_roel_at_hotmail.com>
Date: 13 May 2005 07:31:33 -0700
Message-ID: <1115994693.075261.103130@g49g2000cwa.googlegroups.com>

I am using C# and corelab database library to call package procedure from Oracle.

The error "ORA-01453: SET TRANSACTION must be first statement of transaction" occured when I change a stored procedure in a package body to use a table over a dblink in stead of a table on any schema on the current server.

I found out that corelab sends a SET TRANSACTION statement to the server when it starts a transaction. I then managed to write a very simple script that demonstrates this problem.

--

begin
declare cursor test is
select <field> from <table>@<dblink> where <field> = 4; begin

	set transaction read only;
	open test;
	close test;
	commit;

end;
end;
--

Execute the script (for instance, with Toad). It will generate the error. If you Commit (for instance, by using the button 'Commit' in Toad). Then the error will not occur again. No matter how often the script is run. If you change something in the select statement, for instance the 4 into 5, the error will occur again. Does this suggests that it has something to do with the creation of a query plan?

I would think that using a dblink was transparent, so this is a bug. Can anybody comment on this? I have access to Oracle support, can someone who can reproduce this report it?

Thanks,

Roel Schreurs Received on Fri May 13 2005 - 09:31:33 CDT