Joget || Hibernate issue with Oracle Database

In this post; I'll explain issues we have faced while integrating Joget workflow with oracle database.

Joget is an open source framework to build dynamic forms and manage it throw its workflow engine.

By default Joget comes with MySql database and all functionality works perfectly, after  migrating MySql to Oracle database by following steps mentioned in Joget documentation, we faced below issues:-

• SQL script provided missed some columns, and we have to add them manually.
• Form submission not working for Forms contains more than one elements.

This post will explain in details investigation results for second issue.

By checking logs we found below exception

ERROR 11 Apr 2017 10:36:45 org.joget.apps.form.service.FormService - Error executing store binder
org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80)
...................................
at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

and by investigating exception it appears that form tables not created on the fly and this is due to issue in hibernate Oracledialect as below:-

• OracleDialcet by default converts "Text" to "Long" sql type, which is deprecated by Oracle.
• Oracle has limitation on having "Long" type, as each table can only has one "Long" Column.

So to solve this issue; we have to write custom dialect that override default dialect as below:-

public class OracleCustomDialect extends Oracle10gDialect {

 public OracleCustomDialect() {
  super();
  registerColumnType(Types.VARBINARY, 2000, "raw($l)");
  registerColumnType(Types.VARBINARY, "blob");
  registerColumnType(Types.LONGVARCHAR, "clob");
  registerColumnType(Types.LONGVARBINARY, "blob");
  registerColumnType(Types.VARCHAR, 4000, "varchar2($l char)");
  registerColumnType(Types.VARCHAR, "clob");
 }
}