How to set Oracle nls_date_format using Class::DBI

Question

Hi there,

I followed the example on wiki.class-dbi.com to alter the Oracle session but it does not seem to work. Below is a snippet from my code (strictures and warnings removed for clarity of the problem).

package MyDB;
use base 'Class::DBI::Oracle';

__PACKAGE__->connection('dbi:Oracle:dbname', 'user', 'password');
__PACKAGE__->db_Main->do(q[alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss']);
1;

package MyDB::TestTable;
use base MyDB;

__PACKAGE__->table('my_test_table');
__PACKAGE__->columns(.....);
1;

package main;
use MyDB;

my @foos = MyDB::TestTable->search( seq_number => 1 );
foreach my $foo ( @foos ) {
    say  $foo->created_time;
}

However the result is not what I expect as in yyyy-mm-dd hh24:mi:ss :

Fri May 19 19:58:12 2006

That "alter session" statement using the DBI directly as opposed to CDBI works.

What may cause the problem here?

Thanks.

Answer 1

DBIx::Class provides the on_connect_do hook. You look like you are just starting out with a Perl ORM - you should switch to the good one.

@DSN = (
    'dbi:Oracle:…',
    '…',
    '…',
    {
        AutoCommit    => 1,
        on_connect_do => [
            qq[ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'],
            qq[ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'],
            qq[ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT  = 'YYYY-MM-DD HH24:MI:SS TZHTZM'],
        ],
    }
)