Solutions to problems migrating a Hibernate application to Oracle

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:

  1. Change the hibernate.show_sql property to true to show the generated queries
  2. Change the hibernate.hbm2ddl.auto property to create to force to create the tables
  3. Deploy again and look for the create query related to the table that does not exist
  4. Execute it on a tool like Toad or SQL Developer

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.

Rafael Borrego

Software engineer specialised in working for startups and helping them grow.

Disclaimer: the posts are based on my own experience and may not reflect the views of my current or any previous employer

Facebook Twitter LinkedIn 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>