Re: [Gusdev-gusdev] GBParser Failures...:(

[Jonathan C. <cra...@pc...>]

Michael, Steve-

MICHAEL LUCHTAN wrote:
> Here is the first occurence of the dbdbihandle error:
> Inserting:
> Table: NAEntry
> 	created_date: [2003-5-03 00:00:00]   <--------
> 	row_user_id: [6]

This is definitely a date formatting problem, and this is also a known
problem, but one that I thought had been fixed a while ago.  We use a
nonstandard default date format (NLS_DATE_FORMAT) in our Oracle instance,
namely 'YYYY-MM-DD HH24:MI:SS'.  I believe the default Oracle date format
is 'DD--MON-RR' instead.  Clearly the Perl object layer is generating
date literals of the form 'YYYY-MM-DD HH24:MI:SS'; since this does not
match the default format of your Oracle instance (your NLS_DATE_FORMAT),
an error results.

I described a simple fix for this problem when it was first identified,
and I thought it had been applied to the code, but perhaps there was some
complication that I'm forgetting.  Anyway, one solution is to continue
generating dates in a very specific format, but to use the Oracle TO_DATE
function to explicitly tell Oracle what format you're using, rather than
relying on the implicit string -> date conversion (which relies on the
NLS_DATE_FORMAT).  So for example, instead of doing the following:

insert into sample_table1 values ('2003-5-03 00:00:00');

You'd use the following SQL instead:

insert into sample_table1 values (to_date('2003-5-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

In the object layer this would probably mean replacing "?" in the insert/update
statement with "to_date(?, 'YYYY-MM-DD HH24:MI:SS')".  It may be that the
object layer doesn't distinguish between DATEs and strings for the purposes
of quoting, which might make this nontrivial to implement.  If so then an
alternative would be to have the object layer run the following command at
the beginning of each session:

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Jonathan