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

On 13 May 2005 07:31:33 -0700, schreurs_roel_at_hotmail.com wrote:

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

Most tools send a few free of charge select statements to the server when you connect. Those tools do that implicitly before they hand over control to the end-user program. The first select statement is implicitly the begin of a transaction.
Also a query plans are not generated when you compile PL/SQL, they are generated at run time.
Consequently the problem is not with Oracle, but with the Corelab product, which issues fully unnecessary 'set transaction read only' statements. As you apparently can't change that, the only workaround available is issuing a commit before the set transaction statement. Regrettably, both the commit and the set transaction statement contribute in making your application unscalable. Your last remark is strange, given you do have access to Metalink. Why would anyone post this on your behalf, if you have a reproducible case available.
However, I am sure OTS will state you are not looking at an Oracle bug.

Hth

--
Sybrand Bakker, Senior Oracle DBA