6

I was not able to find a solution for this question online, so I hope I can find help here.

I've inherited a Java web application that performs changes to an Oracle database and displays data from it. The application uses a 'pamsdb' user ID. The application inserted a new row in the one of the tables (TTECHNOLOGY). When the application later queries the db, the result set includes the new row (I can see it in print outs and in the application screens). However, when I query the database directly using sqldeveloper (using the same user id 'pamsdb'), I do not see the new row in the modified table.

A couple of notes:

1) I read here and in other locations that all INSERT operations should be followed by a COMMIT, otherwise other users cannot see the changes. The Java application does not do COMMIT, which I thought could be the source of the problem, but since I'm using the same user ID in sqldeveloper, I'm surprised I can't see the changes there.

2) I tried doing COMMIT WORK from sqldeveloper, but it didn't change my situation.

Can anyone suggest what's causing the discrepancy and how can it be resolved?

Thanks in advance!

Community
  • 1
  • 1
Tamara Aviv
  • 885
  • 1
  • 11
  • 28

1 Answers1

8

You're using the same user, but in a different session. Once session can't see uncommitted changes made in another session, for any user - they are independent.

You have to commit from the session that did the insert - i.e. your Java code has to commit for its changes to be visible anywhere else. You can't make the Java session's changes commit from elsewhere, and committing from SQL Developer - even as the same user - only commits any changes made in that session.

You can read more about connections and sessions, and transactions, and the commit documentation summarises as:

Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement also erases all savepoints in the transaction and releases transaction locks.

Until you commit a transaction:

  • You can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. After you commit the transaction, the changes are visible to other users' statements that execute after the commit.
  • You can roll back (undo) any changes made during the transaction with the ROLLBACK statement (see ROLLBACK).

The "other users cannot see the changes" really means other user sessions.

If the changes are being committed and are visible from a new session via your Java code (after the web application and/or its connection pool have been restarted), but are still not visible from SQL Developer; or changes made directly in SQL Developer (and committed there) are not visible to the Java session - then the changes are being made either in different databases, or in different schemas for the same database, or (less likely) are being hidden by VPD. That should be obvious from the connection settings being used by the two sessions.

From comments it seems that was the issue here, with the Java web application and SQL Developer accessing different schemas which both had the same tables.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 1
    Thanks for your answer, @AlexPoole. I assume that the 'session' that inserted the new data has already terminated (the application ran and completed). Is there any way I could intercept the already inserted data and commit it? – Tamara Aviv Mar 17 '16 at 18:37
  • No, that session would have to commit. If your Java program exited without committing (and has autocommit off, as seems to be the case - which is a good thing generally) then any changes it made have been rolled back and are lost. If you've inherited this then it's odd that it doesn't commit; maybe you're running in a new dev/test environment that is set up differently? – Alex Poole Mar 17 '16 at 18:44
  • Well, there is [LogMiner](https://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL019), if that's available to you, but that might be outside the scope of your question. – Alex Poole Mar 17 '16 at 18:57
  • Alex, wrt to your second comment, I've looked again at some run time parameters and here's what I see: 1. The autoCommit of the connection object is set by default to true, and is not modified by the application after the instantiation. Unless I'm missing something. 2. The application can repeatedly pull the new data and display it, so the changes are not lost. 3. I updated the same queries to have COMMIT after each insert, and ran it on new data, and I'm still not able to see the new data directly in the DB, while the application displays it... – Tamara Aviv Mar 17 '16 at 19:54
  • So if you exit and relaunch the application it can still see its data? Are you sure SQL Developer is connected to the same database then? Compare the JDBC URL with the SQL Developer connection settings. – Alex Poole Mar 17 '16 at 19:58
  • Ale.x, thanks for you detailed help with this question. It seems that there maybe an underlying issue with the db, because I am also not able to see new rows that I add and commit directly from SQL developer. The same happens when my colleague tries to add rows. We've engaged the db team, and hopefully we can understand what happens there. The surprising part is that the inherited web app is able to show the new entries it added, even when it's relaunched and when the server is restarted... I'll revisit the question soon, and accept the answer if needed. – Tamara Aviv Mar 22 '16 at 14:36
  • @TamaraAviv - you can't see new rows you add (and commit!) in SQL Developer from SQL Developer itself, or from the web app? I'm pretty sure you're looking at two different databases or schemas. – Alex Poole Mar 22 '16 at 14:53
  • Alex, that is correct! During the migration process of the DB, two identical schemas were created - the application accessed one, and the SQL Developer accessed another.. Silly, but kept us non-DB experts wide eyed for a few days.. I can edit your answer to add this bit (or you can edit it yourself), because I think it's useful for newbies like me. – Tamara Aviv Mar 22 '16 at 15:46