We have been asked to migrate to Oracle an application that was working on MySql and SQL Server. These are some of the problems we found and how we solved them:
The dialect class was not found
The dialect classes allow Hibernate to work with different types of database. The problem was that Java was throwing a ClassNotFoundException because it could not find the new one. At first we thought that the problem was that the class was on a different path or that the name was misspelled, but both were fine. The reason of the fail was that we have the database configuration written in a properties file to make it easier to change, and there was a blank space after the class name.
ORA-01882: timezone region not found
It was necessary to define the timezone. Initially we changed this on Eclipse at Run configurations → Arguments → VM adding “-Duser.timezone=”Europe/Madrid” and then we changed it in Tomcat like this.
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
This means that it is incorrect the SID of the connection url, that is the word specified after the port.
ORA-00942: table or view does not exist
The message is very clear, a table was not created during the deployment. These are some of possible causes:
- The name of a table is the same as the name of a system one like SYS_USER
- The name of a column is one of the reserved keywords like password or year
Hibernate does not throw an exception if it can not create a table during the deployment so to find out why a create query has failed:
Change the hibernate.show_sql property to true to show the generated queries
Change the hibernate.hbm2ddl.auto property to create to force to create the tables
Deploy again and look for the create query related to the table that does not exist
ORA-00928: missing SELECT keyword
A CRUD query contains the name of a column that is equals to a reserved keyword so it can parse the query
ORA-01747: invalid user.table.column, table.column, or column specification
Another problem with a column name that is equals to a reserved keyword.
Error defining boolean fields
We used to define the boolean fields as:
@Column(name = C_COLUMN_NAME, columnDefinition = "BIT", nullable = false) private Boolean fieldName;
so they could only have the values 0 or 1 but Hibernate does not allow to define columns as bits. So we changed the type to tiny int:
@Column(name = C_COLUMN_NAME, columnDefinition = "TINYINT(1)", nullable = false) private Boolean fieldName;
Parameterized queries didn’t return the expected results
The application also reads from the database of a legacy application using prepared statements (without Hibernate) and we found that some of them were not returning results. The reason was that was some text columns were not defined as varchar, you can find more information here. Fortunately we had tests that alerted us about this error because it would have been difficult to detect it.
As you can see there may be many errors migrating a database although Hibernate does a great work. I invite you to leave comments with other errors you had.