Working With Oracle Date Fields

From ClassDBI

Loading a detailed date field from Oracle.

Problem: The default date format for Oracle as defined by the NLS is often something like dd-mmm-yyyy. However, you sometimes need more precision than that. Here's one way to achieve that without bugging your DBA to change the default.

Solution: Alter your current session to specify a different default date format.

Details: Execute the following statement on your database handle:

 $dbh->do("ALTER SESSION SET NLS_DATE_FORMAT = 'DD:MM:YYYY:HH24:MI:SS'");

If you're overriding the db_Main method, you can run it right after you create the database handle. Just remember to return the database handle as the last statement.

To set it globally, from inside your base CDBI class:

__PACKAGE__->db_Main->do(q[alter session set nls_date_format = 'yyyy/mm/dd
hh24:mi:ss']);

See also Setting Session Globals.

Refer to the Oracle documentation for valid date formats. They are the same ones you can use with a TO_CHAR statement.

After you run this, for the rest of the current session, all dates will come back from the database in the format you define. This allows you to use the Time::Piece examples from the CDBI docs. For example, for the format above, you can add the following method to a CDBI class that has a date object:

 __PACKAGE__->has_a(the_date => 'Time::Piece',]
   inflate => sub { Time::Piece->strptime( shift => "%d:%m:%Y:%H:%M:%S" ) },
 );

When you reference 'the_date', you'll have a Time::Piece object with resolution down to seconds.

Caveats:

If you are using Class::DBI in a mod_perl environment with Apache::DBI pooling database handles you may have other non CDBI applications which use dates and expect the them in a particular format.

---

Using a date field in a primary key

Problem: You have a date field as part of your primary key, but when you fetch records, the date fields are not set in your result set.

Solution: Don't use the TO_CHAR function in your set_sql statement when selecting records. If you need a date format different from the default, consider the solution described above for re-setting the default date format for your session.

Details: CDBI seems to handle date fields in the primary key OK. However, if you are fetching records using a select statement in a set_sql function, it doesn't seems to like Oracle functions like TO_CHAR. I had an example like this:

 __PACKAGE__->set_sql(user_notes => qq{
 select order_id,
          TO_CHAR(enter_date, 'DD:MM:YYYY:HH24:MI:SS'),
          user,
          comments
      from notes
     where order_id = ?
  order by enter_date
 });

It would run, but the enter_date column would come back empty.

One work-around, as described above, is to change the default format for dates in your session so the date comes back as you want it.

Another work-around is to move the date field from the Primary section in your CDBI object to the Essential section. In this case you will get values back. However, if the date is truly part of your primary key this will cause problems because you will likely get the same date for several different records. This isn't a bug; CDBI is being correctly lazy since it thinks the date fields are not part of the primary key.