Check the DateTime::Format::Oracle documentation for the NLS environment
variables
(http://search.cpan.org/~kolibrie/DateTime-Format-Oracle-0.05/lib/DateTime/Format/Oracle.pm).
They control the DateTime's string format for the 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'} = 'YYYY-MM-DD';
$ENV{'NLS_TIMESTAMP_FORMAT'} = 'YYYY-MM-DD HH24:MI:SS';
...then we use this connect option to make the Oracle ones match
on_connect_do => [
'ALTER SESSION SET NLS_DATE_FORMAT = \'YYYY-MM-DD\'',
'ALTER SESSION SET NLS_TIMESTAMP_FORMAT = \'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
distinction, 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’t work. I began it
> wonder if it could recognise the standard Oracle DATE column type.
> All the best.
> Duncan
>
> From: Dan Horne [mailto:[email protected]]
> 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 <[email protected]> wrote:
> Hi,
>
> I’m struggling to persuade DBIx::Class to use simple Oracle built-ins such as
> SYSDATE, DECODE and NVL.
>
> Eg UPDATE table1 SET date_updated = SYSDATE, destination = NVL( $destination,
> ‘home’ ) WHERE ... ;
>
> $rs->date_updated( ‘SYSDATE’ );
>
> Doesn’t work, nor can any variation on a theme that I can think of.
>
> I’m sure it must be possible and I’m sure it must be in the docs, but I can’t
> find it.
>
> Can anybody help?
>
> Regards
>
> Duncan
>
> I do most of my development against Oracle, although I try to make my code DB
> generic where possible. If you use a DateTime object rather than sysdate,
> DBIC will deflate it for you. Of course, creating the new object is slower
> than simply using sysdate, so I guess it depends how speed sensitive your 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/[email protected]
_______________________________________________
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/[email protected]
