[Dbix-class] Oracle Built-In Functions

[Duncan Garland]

Hi,

Yes, that works.

Thanks

From: Ryan Cone [mailto:metrext at gmail.com]
Sent: 05 August 2010 13:38
To: DBIx::Class user and developer list
Subject: Re: [Dbix-class] Oracle Built-In Functions

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: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<mailto:Duncan.Garland at motortrak.com>> 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/dbix-class@lists.scsys.co.uk

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20100806/a7e9b516/attachment-0001.htm