DateTime::Format::Oracle(3pm)

1DateTime::Format::OraclUes(e3r)Contributed Perl DocumentDaattieoTnime::Format::Oracle(3)
2
3
4

NAME

6       DateTime::Format::Oracle - Parse and format Oracle dates and timestamps
7

SYNOPSIS

9         use DateTime::Format::Oracle;
10
11         $ENV{'NLS_DATE_FORMAT'} = 'YYYY-MM-DD HH24:MI:SS';
12         my $dt = DateTime::Format::Oracle->parse_datetime('2003-01-16 23:12:01');
13         my $string = DateTime::Format::Oracle->format_datetime($dt);
14

DESCRIPTION

16       This module may be used to convert Oracle date and timestamp values
17       into "DateTime" objects.  It also can take a "DateTime" object and pro‐
18       duce a date string matching the "NLS_DATE_FORMAT".
19
20       Oracle has flexible date formatting via its "NLS_DATE_FORMAT" session
21       variable.  Date values will be returned from Oracle according to the
22       current value of that variable.  Date values going into Oracle must
23       also match the current setting of "NLS_DATE_FORMAT".
24
25       Timestamp values will match either the "NLS_TIMESTAMP_FORMAT" or
26       "NLS_TIMESTAMP_TZ_FORMAT" session variables.
27
28       This module keeps track of these Oracle session variable values by
29       examining environment variables of the same name.  Each time one of
30       Oracle's formatting session variables is updated, the %ENV hash must
31       also be updated.
32

METHODS

34       This class offers the following methods.
35
36       * nls_date_format
37           This method is used to determine the current value of Oracle's
38           "NLS_DATE_FORMAT".  It currently just reads the value from
39
40             $ENV{'NLS_DATE_FORMAT'}
41
42           or if that is not set, from the package variable $nls_date_format,
43           which has a default value of "YYYY-MM-DD HH24:MI:SS".  This is a
44           good default to have, but is not Oracle's default.  Dates will fail
45           to parse if Oracle's NLS_DATE_FORMAT and the value from this method
46           are not the same.
47
48           If you want to use the default from this module, you can do some‐
49           thing like this after you connect to Oracle:
50
51             $dbh->do(
52                 "alter session set nls_date_format = '" .
53                 DateTime::Format::Oracle->nls_date_format .
54                 "'"
55             );
56
57       * parse_datetime
58           Given a string containing a date and/or time representation match‐
59           ing "NLS_DATE_FORMAT", this method will return a new "DateTime"
60           object.
61
62           If given an improperly formatted string, this method may die.
63
64       * parse_date
65           Alias to "parse_datetime".  Oracle's date datatype also holds time
66           information.
67
68       * current_date_parser
69           The current "DateTime::Format::Builder" generated parsing method
70           used by "parse_datetime" and "parse_date".
71
72       * format_datetime
73           Given a "DateTime" object, this method returns a string matching
74           the current value of "NLS_DATE_FORMAT".
75
76           It is important to keep the value of $ENV{'NLS_DATE_FORMAT'} the
77           same as the value of the Oracle session variable "NLS_DATE_FORMAT".
78
79           To determine the current value of Oracle's "NLS_DATE_FORMAT":
80
81             select NLS_DATE_FORMAT from NLS_SESSION_PARAMETERS
82
83           To reset Oracle's "NLS_DATE_FORMAT":
84
85             alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
86
87           It is generally a good idea to set "NLS_DATE_FORMAT" to an unam‐
88           biguos value, with four-digit year, and hour, minute, and second.
89
90       * format_date
91           Alias to "format_datetime".
92
93       * current_date_format
94           The current generated method used by "format_datetime", "for‐
95           mat_date", and "current_date_parser" to keep track of the "strp‐
96           time" translation of "NLS_DATE_FORMAT".
97
98       * oracle_to_posix
99           Given an "NLS_DATE_FORMAT", "NLS_TIMESTAMP_FORMAT", or "NLS_TIME‐
100           STAMP_TZ_FORMAT" value, this method returns a "DateTime"-compatible
101           "strptime" format value.
102
103           Translation is currently handled by "Convert::NLS_DATE_FORMAT".
104

LIMITATIONS

106       Oracle is more flexible with the case of names, such as the month,
107       whereas "DateTime" generally returns names in "ucfirst" format.
108
109         MONTH -> FEBRUARY
110         Month -> February
111         month -> february
112
113       All translate to:
114
115         %B    -> February
116
117       TIME ZONES
118
119       Oracle returns all dates and timestamps in a time zone similar to the
120       "DateTime" floating time zone, except for 'timestamp with time zone'
121       columns.
122
123       I have not yet implemented "parse_timestamp", "format_timestamp",
124       "parse_timestamp_with_timezone", nor "format_timestamp_with_timezone".
125
126       INTERVAL ELEMENTS
127
128       I have not implemented "parse_duration", "format_duration",
129       "parse_interval", nor "format_interval", and have no plans to do so.
130
131       If you need these features, unit tests, method implementations, and
132       pointers to documentation are all welcome.
133

SUPPORT

135       Support for this module is provided via the datetime@perl.org email
136       list.  See http://lists.perl.org/ for more details.
137

TODO

139       Possibly read an environment variable to determine a time zone to use
140       instead of 'floating'.
141
142       Test and document creating an instance via "new".
143

AUTHOR

145       Nathan Gray, <kolibrie@cpan.org>
146

ACKNOWLEDGEMENTS

148       I might have put this module off for another couple years without the
149       lure of Jifty, Catalyst, and DBIx::Class pulling at me.
150
151       Thanks to Dan Horne for his RFC draft of this module.
152

COPYRIGHT & LICENSE

154       Copyright (C) 2006 Nathan Gray.
155
156       This library is free software; you can redistribute it and/or modify it
157       under the same terms as Perl itself, either Perl version 5.8.4 or, at
158       your option, any later version of Perl 5 you may have available.
159