Forums Active Threads
Print Thread
DBLink and transactions?
#59914 04/12/19 10:31 AM
Joined: Jun 2018
Posts: 46
Ukraine, Odessa
B
Buzz_ss Offline OP
Member
OP Offline
Buzz_ss
Member
B
Joined: Jun 2018
Posts: 46
Ukraine, Odessa
Hello.


I execute SQL
SQL Query
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

Last edited by Buzz_ss; 04/12/19 10:44 AM.
Sergiy Buzadzhi
Re: DBLink and transactions?
Buzz_ss #59916 04/12/19 12:14 PM
Joined: Aug 1999
Posts: 22,221
Marco Kalter Offline
Member
Offline
Marco Kalter
Member
Joined: Aug 1999
Posts: 22,221
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.

Marco Kalter
Allround Automations
Re: DBLink and transactions?
Marco Kalter #59918 04/12/19 01:04 PM
Joined: Jun 2018
Posts: 46
Ukraine, Odessa
B
Buzz_ss Offline OP
Member
OP Offline
Buzz_ss
Member
B
Joined: Jun 2018
Posts: 46
Ukraine, Odessa
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 :

SQL Query
 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:

Code
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.

Last edited by Buzz_ss; 04/12/19 01:18 PM.
Sergiy Buzadzhi
Re: DBLink and transactions?
Buzz_ss #59921 04/12/19 06:02 PM
Joined: Aug 1999
Posts: 22,221
Marco Kalter Offline
Member
Offline
Marco Kalter
Member
Joined: Aug 1999
Posts: 22,221
We'll check it out. As a workaround you can set a savepoint before executing the query:
Code
savepoint dummy;
select cursor(select * from My_postgress_view 
 where rownum <100) as TT    from dual;

Marco Kalter
Allround Automations

Moderated by  support 

Link Copied to Clipboard
Privacy Policy · Forum Rules · Mark All Read Contact Us · Forum Help ·
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.021s Queries: 14 (0.005s) Memory: 2.5119 MB (Peak: 3.0427 MB) Data Comp: Off Server Time: 2024-05-20 16:50:38 UTC
Valid HTML 5 and Valid CSS