Session refresh errors with ODBC?

[Erik-Berndt Scheper]

Hi,

We encountered the following problem with PL/SQL Developer. If I add/delete a record using ODBC (using autocommit), then we can see the change in sqlplus.

However, in PL/SQL Developer using multi-session mode, the changes are only seen after closing the application, and restarting PL/SQL Developer.
E.g. it does not even suffice to start a new Sql-window!

But if we change PL/SQL Developer to single-session mode, then all changes are reflected after a refresh of the query.

Any ideas? Is this an OCI-bug? Or PL/SQL Developer specific. Toad does not seem to suffer from the same behaviour.

 

[Marco Kalter]

If you use "Multi Session Mode", then each window will use its own session. Consequently, changes made in one window will only be visible in another window if you commit the transaction in the first window. There is no need to restart PL/SQL Developer though, a commit is sufficient.

If you want to see uncommitted changes across different windows, you will need to switch to "Dual Session Mode" (Tools > Preferences > Oracle/Options). Now all windows will use the same session, though all DDL activities (Program Window, Table Editor, and so on) will be performed in a different session. This prevents that a editing an object definition implicitly commits your transaction.

 

[Erik-Berndt Scheper]

I'm afraid you misunderstood the problem. I understand that in multisession operation, each window within PL/SQL Developer uses its own session. So changes in one pl/sql developer session are not reflected in another, until the first session issued a commit.

But our problem is that the data changes were not done in a session of PL/SQL Developer, but in a separate (custom built) Visual Basic application using ODBC.

I would say that (autocommitted) changes in this VB-application should have been visible in any PL/SQL Developer session.

However (in multi-session mode) they were not visible until PL/SQL developer had been closed and restarted.
Note: Single session mode of PL/SQL Developer did not suffer from this problem.

 

[Marco Kalter]

Very strange. It seems as though the transaction isolation level is set to serializable in PL/SQL Developer. Could this be the case? If so, executing a commit in the SQL Window and subsequently requerying the updated data should do the trick.