oracle-databaseoracle19crowcount

oracle 19c - row count is different using 2 different users - why?


Something very strange has happened since we migrated our schemas/database from 12c to 19c

When I insert records to a table and I check the row count namely under my oracle user - say SMITH_J - I see 4 records. Good I am happy.

When my Java application looks at the same table which I will call QUEUE_TAB - using the application oracle user - say APP_TOMCAT - it just sees ZERO records. How can that be ?

I check the GRANTS for APP_TOMCAT - it has everything that should be there for that table - it's got SELECT,INSERT,UPDATE,DELETE - which it had before.

What is really perplexing why are the record counts different - despite all the privileges being the same ? Is there something here that I have overlooked OR cannot see at the moment ? Is it something to do with privileges going from 12c to 19c ?


Solution

  • I owe the correct answer to Alex Poole in the comments above.

    I was using a procedure to populate the table in question. And, foolishly assumed that the procedure would commit it. Of, course, it would have if it DID have a COMMIT within the code at the end of the procedure. So, after EXECUTING the procedure - I issued a COMMIT and it worked.

    It's best practice NOT to have a COMMIT statement within your procedure even if it is a single procedure. See the comments of @MTO and @Alex Poole below