[Dbix-class] Oracle Built-In Functions

[Ryan Cone]

Check the DateTime::Format::Oracle documentation for the NLS environment va=
riables (http://search.cpan.org/~kolibrie/DateTime-Format-Oracle-0.05/lib/D=
ateTime/Format/Oracle.pm).  They control the DateTime's string format for t=
he format and parse.  You will also need to set the Oracle session to match=
 NLS or the to_date it calls behind the scenes will break.

The disagreement between NLS ENV and Session formats is probably the cause =
of the DateTime->now not working and also the culprit for truncating your \=
'SYSDATE' from before.

We use these options:

$ENV{'NLS_DATE_FORMAT'} =3D 'YYYY-MM-DD';
$ENV{'NLS_TIMESTAMP_FORMAT'} =3D 'YYYY-MM-DD HH24:MI:SS';

...then we use this connect option to make the Oracle ones match

on_connect_do =3D> [
    'ALTER SESSION SET NLS_DATE_FORMAT =3D \'YYYY-MM-DD\'',
    'ALTER SESSION SET NLS_TIMESTAMP_FORMAT =3D \'YYYY-MM-DD HH24:MI:SS\''
]


This will give you Oracle DATE and DATETIME columns with date precision and=
 Oracle TIMESTAMP columns with time precision.  If you don't need the disti=
nction, setting NLS_DATE_FORMAT to time precision should fix the DateTime->=
now and the SYSDATE truncation.

-Ryan




On Aug 5, 2010, at 4:00 AM, Duncan Garland wrote:

> Hi,
> I tried
> $rs->date_updated( DateTime->now );
> And several other variations on the theme and they didn=92t work. I began=
 it wonder if it could recognise the standard Oracle DATE column type.
> All the best.
> Duncan
>  =

> From: Dan Horne [mailto:dan.horne at redbone.co.nz] =

> Sent: 04 August 2010 23:41
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] Oracle Built-In Functions
>  =

>  =

> =

> On 4 August 2010 22:25, Duncan Garland <Duncan.Garland at motortrak.com> wro=
te:
> Hi,
>  =

> I=92m struggling to persuade DBIx::Class to use simple Oracle built-ins s=
uch as SYSDATE, DECODE and NVL.
>  =

> Eg UPDATE table1 SET date_updated =3D SYSDATE, destination =3D NVL( $dest=
ination, =91home=92 ) WHERE ... ;
>  =

> $rs->date_updated( =91SYSDATE=92 );
>  =

> Doesn=92t work, nor can any variation on a theme that I can think of.
>  =

> I=92m sure it must be possible and I=92m sure it must be in the docs, but=
 I can=92t find it.
>  =

> Can anybody help?
>  =

> Regards
>  =

> Duncan
>  =

> I do most of my development against Oracle, although I try to make my cod=
e DB generic where possible. If you use a DateTime object rather than sysda=
te, DBIC will deflate it for you. Of course, creating the new object is slo=
wer than simply using sysdate, so I guess it depends how speed sensitive yo=
ur app is...
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.=
co.uk

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20100805/ab2=
20bc1/attachment.htm