OFBiz and Oracle

Upgrade to the latest JDBC driver available

In general, this is the first thing you should attempt to solve your db problems: get the latest JDBC driver available and install it in the framework/entity/lib/jdbc folder.
Many issues will disappear if you use a recent driver.

How to set the format of Timestamps

If you are getting errors about "ORA-01843: not a valid month" for example when OFBiz tries to update the JobSandbox entity, this is caused by the default format Oracle uses for Timestamp fields; unfortunately there is not a way (that I'm aware of) to pass the Java Timestamp format (that is the one used by OFBiz) to the JDBC driver.

The following trigger will do the trick by setting the right format every time the db user (in this example 'ofbiz') will connect to the db: this user must be the one you set in the entityengine.xml file:

create or replace TRIGGER ON_CONNECT AFTER LOGON ON DATABASE DECLARE
 guser varchar2(30);
 begin
 SELECT sys_context('USERENV','SESSION_USER') into guser FROM dual;

 

if guser='ofbiz' THEN
 EXECUTE IMMEDIATE 'alter session set nls_timestamp_format = ''YYYY-MM-DD HH24:MI:SS.FF''';
 end
 if
 ;
end;

Thanks to Giorgio Tomaselli for this script.

This solution is valid for the "ORA-01830: date format picture ends before converting entire input string" error too in Oracle 10g.

How to set the format of Dates

If you are getting errors about "ORA-01861: literal does not match format string", this is caused by the default format Oracle uses for Timestamp fields; this is a problem with the date conversion.

Here is how to properly set the Oracle Date Format:

ALTER SYSTEM set NLS_TIMESTAMP_FORMAT='RRRR-MM-DD HH24:MI:SS.FF'

You would also be interested in OFBIZ-1001.

How to avoid NLS related errors

By default the OFBiz's startup scripts only set the user.language JVM property; however if you are getting errors by the Oracle driver about unknown locale (NLS), it can be fixed by setting the user.country JVM property (you can do this in the OFBiz start script):

-Duser.language=en -Duser.country=US

How to deal with startup warnings about missing tables/columns/keys

You can safely ignore the warning messages.

However, here is a note from David E. Jones with more information about the issue:

"It looks like your JDBC driver is not getting the meta data it needs for this. If you want it to work, check out DatabaseUtil.java and make changes there. The simplest way to avoid the warnings is to turn the feature off using the attributes on the datasource element in the entityengine.xml file."