Oracle tips

[Jacques Le Roux]

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

[Jacopo Cappellato-4]

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
>
>

[Jacopo Cappellato-4]

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
>>
>>
>

[Jacques Le Roux]

Thanks Jacopo,

Added at http://cwiki.apache.org/confluence/display/OFBIZ/OFBiz+and+Oracle as a FAQ child page

Jacques

From: "Jacopo Cappellato" <[hidden email]>

> 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
>>>
>>>
>>
>
>