Thursday, January 27, 2011

Setting up Grails project on Oracle XE database

If you have some code already working on another database, say Postgres, and you want to set it up on Oracle XE database, there are some things to consider.

You have to change the application data source (conf/DataSource.groovy) and move the Oracle jdbc driver to /lib - all that is explained pretty well here.

Basically the result will be something like this :
production {
   dataSource {
       dbCreate = "update" // or create, create-drop
      boolean pooled = true
      driverClassName = "oracle.jdbc.driver.OracleDriver"
      username = "[the username]"
      password = "[the password]"
      dialect= "org.hibernate.dialect.Oracle9Dialect" // or "org.hibernate.dialect.OracleDialect"
      url = "jdbc:oracle:thin:@127.0.0.1:1521:XE"
  }
}
You might have some troubles with the url, here is a little explanation :
I was running the XE database on my local host, hence the address 127.0.0.1.

XE is the default SID for Oracle XE.

The 1521 is the default database listener port for Oracle XE.

Oracle XE and port setup

While we are talking about ports. The default http interface port for Oracle XE management through APEX is 8080 and it might be in collision with other software you are running, like our grails app.

You can read on here to find out the way to check default ports and those set on your configuration and also how to change them.

Oracle XE and reserved words

If you are seeing a lot of stack traces while running your grails app on the new configuration, chances are that your domain classes use field names that are reserved in a Oracle database. Try to stop the start up before all the stacktraces. If you see something like "table could not be created" and "ORA-00904 Invalid identifier" run  "select * from v$reserved_words" and check if your domain names or field names collide with the reserved words.