DBLink and transactions?

[Buzz_ss]

Hello.

I execute SQL

select * from my_Table@My_DBLINK
where rownum <1000

in SQLWindow. After execute I see that button "Commit" is enabled. It is ok. But after I fetched all records this button becomes disabled. Why does PLSQLDeveloper perform commit? Autocommit is unchecked in my preferences.

Thanks

PS In TestWindow I haven't this problem

 

[Marco Kalter]

If there was no open transaction before the dblink query is executed, the implicit transaction will be committed when all records are fetched. Users do not expect an open transaction after executing a select statement without a "for update" clause.

 

[Buzz_ss]

Excuse me.

But my situation is more complex.
1. I have view that uses DB_Link to Postgress database.
2. I have select like this :

select cursor(select * from My_postgress_view
 where rownum <100) as TT    from dual;

3. I execute this select in SQLWindow. Then I try to see content of cursor field "TT"

Ups... I see next error:

ORA-01002: fetch out of sequence
ORA-02063: preceding line from ....

This error occurs because transaction already commited.
In this situation, I would prefer that the transaction does not commit automatically.

Important : I could not get this error for links Oracle to Oracle.

PS. By the way, if my code opens a transaction, then I would prefer to see it anyway. Otherwise, I can just forget that dblink is used somewhere inside or something else happens.

 

[Marco Kalter]

We'll check it out. As a workaround you can set a savepoint before executing the query:

savepoint dummy;
select cursor(select * from My_postgress_view
 where rownum <100) as TT    from dual;