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

schreurs_r..._at_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_at_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 Received on Fri May 13 2005 - 23:37:02 CDT