Here is the content of that page.
Jacopo
=======================================
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.
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."
=======================================
On Feb 27, 2010, at 8:57 AM, Jacopo Cappellato wrote:
> This is a pain actually... I will try to find out if I can retrieve that from
> somewhere.
>
> Jacopo
>
> On Feb 27, 2010, at 12:23 AM, Jacques Le Roux wrote:
>
>> Hi Jacopo,
>>
>> During the wiki migration we have lost these tips, are they still of some
>> value? Do you have still them available somewhere?
>> http://olddocs.ofbiz.org/display/~jacopoc/OFBiz+and+Oracle
>> Thanks
>>
>> Jacques
>>
>>
>
