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

[schreu...@hotmail.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

[Maxim]

>From the Note
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1070310.6
/////////////////////////////////////////////
Solution Description:
=====================

A remote select (a select using a database link) must be followed by a
commit.
Then the set transaction command will succeed.

Explanation:
============

This is expected behavior. A select or any other operation which uses
a
database link is considered the beginning of a distributed transaction.

Even though there are no inserts, updates, or deletes, a select across
a
database link is considered the beginning of a distributed transaction,
and
therefore must be committed before a new transaction can begin.
//////////////////////////////////////////

Also, if you put the "commit;" on the line before "set transaction" ,
you eliminate the error.

Best regards

Maxim

[Sybrand Bakker]

On 13 May 2005 07:31:33 -0700, schreu...@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

[Joel Garry]

> Your last remark is strange, given you do have access to Metalink

Since I insist on full support access, I keep making this mistake, too.
If you look at user profile --> manage, you see that there are check
boxes:

Add Support Identifier
Remove Support Identifier
Forum
Configure "My Headlines"
Access Patch Download
Read TARs
Open, Update and Close TARs
Freeze (Deactivate) this User
My Configs & Projects Create, Update, Delete

So, obviously there are people who can poke around but not actually ask
support for anything, or maybe not be allowed to post on fora (I can
understand that last one! :-)

I can understand why sites would want to control access like this, as
I've seen developers get out of control, asking vendor support (or
usenet, for that matter) about things when they should be using their
local resources first. My personal preference is to let people have
access to as much as possible, regardless.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/state/20050513-1127-ca-wendysfinger.html#video

[yon...@yahoo.com]

schreurs_r...@hotmail.com wrote:
> --
> 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?

Roel,

I can reproduce your case in 9i and 10g. Here's my code:

rollback;
declare
cursor c is select * from dual@mylink;
n number;
begin
select count(*) into n from v$transaction;
dbms_output.put_line('Number of transactions is ' || n);
end;
/

The first time I run it, I get 1 transaction. Second time 0. If I make
very trivial change such as "end" to "enD", I get 1 again. Run it one
more time (with "enD") and it's 0 again. Then if I change back to
"end", it's back to 0 because I ran it before. Also, if I flush shared
pool in between, I get 1 transaction even if I ran the same block
before.

It looks like when a PL/SQL block that contains a distributed query is
hard parsed, even without opening the cursor, the transaction due to
the distributed query is created. This doesn't apply to plain SQL,
which always creates the transaction even if the text is kept exactly
the same. This behavior may be documented but I can't find it for now.

Yong Huang

[yon...@yahoo.com]

schreu...@hotmail.com wrote:
> --
> 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;
> --

Any progress on this? Since parsing PL/SQL that contains distributed
queries creates a transaction, why not move your set transaction read
only to the outside of the PL/SQL block, above your first begin?

You said CoreLab's software sends set transaction at the beginning of
their transaction. That doesn't sound like sending it as the first line
*inside* a PL/SQL block or stored procedure, which is what you're
simulating.

Yong Huang