0

I have a spring boot application,

I have migrated the database from postres 11 to oracle 19c.

The migration has gone fine, I can see the tables and the rows.

The application starts up normally and even some HT_ tables are created, so the connection is fine. When I try to execute queries via the hibernate I get table not found

SQL Error: 942, SQLState: 42000 ORA-00942 table or view does not exist

I have activated extra logging but that did not really help me.

I can see the tables in the database, so I am not sure how to debug the issue, any suggestions?

Dimitris
  • 560
  • 3
  • 17
  • I guess you have problem with case sensitivity of some table names. – ibre5041 Jun 12 '23 at 11:50
  • @ibre5041 I thought so too, they are all lower case, i copy pasted the value to the spring boot Table annotcation still was not fixed. – Dimitris Jun 12 '23 at 11:54
  • Oracle tables are by default case in-sensitive, all upper case. If you see table name in user_tables view as lower case, then table was created as case sensitive and you have to convince hibernate, that SQL has to use double quotes. When table name is in double quotes, then you tell Oracle that is should treat table name as case sensitive. – ibre5041 Jun 12 '23 at 11:58
  • I will check that thanks – Dimitris Jun 12 '23 at 12:07

1 Answers1

2

In Oracle, identifiers are case-sensitive. However, if you have unquoted identifiers:

SELECT name, age FROM schema_name.table_name;

Then when the SQL engine parses the statement in the database, it will implicitly convert the unquoted identifiers to upper-case and the query is effectively:

SELECT "NAME", "AGE" FROM "SCHEMA_NAME"."TABLE_NAME";

Which means that if you always use unquoted identifiers then you can use any case you want (and the database appears to be case-insensitive with regard to identifiers).

If your identifier are lower-case then you will need to explicitly use lower-case quoted-identifiers:

SELECT "name", "age" FROM "schema_name"."table_name";

In Spring Boot, you can check the logs and see what statement is being generated and try running it in the database and you should see the same error.

To fix it, you can try the suggestions in this question, including:

  • Updating the naming setting in the application properties; or
  • Including quotes in the table/column names in the entity.
MT0
  • 143,790
  • 11
  • 59
  • 117
  • @MTO I let the hibernate handle the queries, I switched on some loggin and I see that the table name at least is matching the correct one (lower case) I will add also the column annotations to see if that will fix it. – Dimitris Jun 12 '23 at 12:07