英文:
Spring database migration to oracle, table is not found
问题
我有一个Spring Boot应用程序，
我已将数据库从PostgreSQL 11迁移到Oracle 19c。
迁移进行得很顺利，我可以看到表和行。
应用程序正常启动，甚至创建了一些HT_表，所以连接正常。但当我尝试通过Hibernate执行查询时，我收到了"table not found"的错误：
SQL错误：942，SQL状态：42000
ORA-00942 表或视图不存在
我已经启用了额外的日志记录，但这并没有真正帮助我。
我可以在数据库中看到表，所以我不确定如何调试这个问题，有什么建议吗？
英文:
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?
答案1
得分: 2
在Oracle中，标识符是区分大小写的。然而，如果你使用未加引号的标识符：
SELECT name, age FROM schema_name.table_name;
那么当SQL引擎解析数据库中的语句时，它会隐式地将未加引号的标识符转换为大写，并且查询实际上是：
SELECT "NAME", "AGE" FROM "SCHEMA_NAME"."TABLE_NAME";
这意味着如果你始终使用未加引号的标识符，那么你可以使用任何大小写（数据库看起来对标识符不区分大小写）。
如果你的标识符是小写的，那么你需要显式使用小写的带引号的标识符：
SELECT "name", "age" FROM "schema_name"."table_name";
在Spring Boot中，你可以检查日志并查看生成的语句，尝试在数据库中运行它，你应该会看到相同的错误。
要修复它，你可以尝试此问题中的建议，包括：
- 更新应用程序属性中的命名设置；或
- 在实体的表/列名称中包含引号。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论