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
18       produce 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
38           This method is used to determine the current value of Oracle's
39           "NLS_DATE_FORMAT".  It currently just reads the value from
40
41             $ENV{'NLS_DATE_FORMAT'}
42
43           or if that is not set, from the package variable $nls_date_format,
44           which has a default value of "YYYY-MM-DD HH24:MI:SS".  This is a
45           good default to have, but is not Oracle's default.  Dates will fail
46           to parse if Oracle's NLS_DATE_FORMAT and the value from this method
47           are not the same.
48
49           If you want to use the default from this module, you can do
50           something like this after you connect to Oracle:
51
52             $dbh->do(
53                 "alter session set nls_date_format = '" .
54                 DateTime::Format::Oracle->nls_date_format .
55                 "'"
56             );
57
58       •   nls_timestamp_format
59
60           This method is used to determine the current value of Oracle's
61           "NLS_TIMESTAMP_FORMAT".  It currently just reads the value from
62
63             $ENV{'NLS_TIMESTAMP_FORMAT'}
64
65           or if that is not set, from the package variable
66           $nls_timestamp_format, which has a default value of "YYYY-MM-DD
67           HH24:MI:SS".  This is a good default to have, but is not Oracle's
68           default.  Dates will fail to parse if Oracle's NLS_TIMESTAMP_FORMAT
69           and the value from this method are not the same.
70
71           If you want to use the default from this module, you can do
72           something like this after you connect to Oracle:
73
74             $dbh->do(
75                 "alter session set nls_timestamp_format = '" .
76                 DateTime::Format::Oracle->nls_timestamp_format .
77                 "'"
78             );
79
80       •   nls_timestamp_tz_format
81
82           This method is used to determine the current value of Oracle's
83           "NLS_TIMESTAMP_TZ_FORMAT".  It currently just reads the value from
84
85             $ENV{'NLS_TIMESTAMP_TZ_FORMAT'}
86
87           or if that is not set, from the package variable
88           $nls_timestamp_tz_format, which has a default value of "YYYY-MM-DD
89           HH24:MI:SS TZHTZM".  This is a good default to have, but is not
90           Oracle's default.  Dates will fail to parse if Oracle's
91           NLS_TIMESTAMP_TZ_FORMAT and the value from this method are not the
92           same.
93
94           If you want to use the default from this module, you can do
95           something like this after you connect to Oracle:
96
97             $dbh->do(
98                 "alter session set nls_timestamp_tz_format = '" .
99                 DateTime::Format::Oracle->nls_timestamp_tz_format .
100                 "'"
101             );
102
103       •   parse_datetime
104
105           Given a string containing a date and/or time representation
106           matching "NLS_DATE_FORMAT", this method will return a new
107           "DateTime" object.
108
109           If given an improperly formatted string, this method may die.
110
111       •   parse_date
112
113           Alias to "parse_datetime".  Oracle's date datatype also holds time
114           information.
115
116       •   parse_timestamp
117
118           Given a string containing a date and/or time representation
119           matching "NLS_TIMESTAMP_FORMAT", this method will return a new
120           "DateTime" object.
121
122           If given an improperly formatted string, this method may die.
123
124       •   parse_timestamptz =item * parse_timestamp_with_time_zone
125
126           Given a string containing a date and/or time representation
127           matching "NLS_TIMESTAMP_TZ_FORMAT", this method will return a new
128           "DateTime" object.
129
130           If given an improperly formatted string, this method may die.
131
132       •   current_date_parser
133
134           The current "DateTime::Format::Builder" generated parsing method
135           used by "parse_datetime" and "parse_date".
136
137       •   current_timestamp_parser
138
139           The current "DateTime::Format::Builder" generated parsing method
140           used by "parse_timestamp".
141
142       •   current_timestamptz_parser
143
144           The current "DateTime::Format::Builder" generated parsing method
145           used by "parse_timestamptz".
146
147       •   format_datetime
148
149           Given a "DateTime" object, this method returns a string matching
150           the current value of "NLS_DATE_FORMAT".
151
152           It is important to keep the value of $ENV{'NLS_DATE_FORMAT'} the
153           same as the value of the Oracle session variable "NLS_DATE_FORMAT".
154
155           To determine the current value of Oracle's "NLS_DATE_FORMAT":
156
157             select NLS_DATE_FORMAT from NLS_SESSION_PARAMETERS
158
159           To reset Oracle's "NLS_DATE_FORMAT":
160
161             alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
162
163           It is generally a good idea to set "NLS_DATE_FORMAT" to an
164           unambiguos value, with four-digit year, and hour, minute, and
165           second.
166
167       •   format_date
168
169           Alias to "format_datetime".
170
171       •   format_timestamp
172
173           Given a "DateTime" object, this method returns a string matching
174           the current value of "NLS_TIMESTAMP_FORMAT".
175
176           It is important to keep the value of $ENV{'NLS_TIMESTAMP_FORMAT'}
177           the same as the value of the Oracle session variable
178           "NLS_TIMESTAMP_FORMAT".
179
180           To determine the current value of Oracle's "NLS_TIMESTAMP_FORMAT":
181
182             select NLS_TIMESTAMP_FORMAT from NLS_SESSION_PARAMETERS
183
184           To reset Oracle's "NLS_TIMESTAMP_FORMAT":
185
186             alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS'
187
188           It is generally a good idea to set "NLS_TIMESTAMP_FORMAT" to an
189           unambiguos value, with four-digit year, and hour, minute, and
190           second.
191
192       •   format_timestamptz =item * format_timestamp_with_time_zone
193
194           Given a "DateTime" object, this method returns a string matching
195           the current value of "NLS_TIMESTAMP_TZ_FORMAT".
196
197           It is important to keep the value of
198           $ENV{'NLS_TIMESTAMP_TZ_FORMAT'} the same as the value of the Oracle
199           session variable "NLS_TIMESTAMP_TZ_FORMAT".
200
201           To determine the current value of Oracle's
202           "NLS_TIMESTAMP_TZ_FORMAT":
203
204             select NLS_TIMESTAMP_TZ_FORMAT from NLS_SESSION_PARAMETERS
205
206           To reset Oracle's "NLS_TIMESTAMP_TZ_FORMAT":
207
208             alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS TZHTZM'
209
210           It is generally a good idea to set "NLS_TIMESTAMP_TZ_FORMAT" to an
211           unambiguos value, with four-digit year, and hour, minute, and
212           second.
213
214       •   current_date_format
215
216           The current generated method used by "format_datetime",
217           "format_date", and "current_date_parser" to keep track of the
218           "strptime" translation of "NLS_DATE_FORMAT".
219
220       •   current_timestamp_format
221
222           The current generated method used by "format_timestamp",
223           "format_timestamp_with_time_zone", and "current_timestamp_parser"
224           to keep track of the "strptime" translation of
225           "NLS_TIMESTAMP_FORMAT".
226
227       •   current_timestamptz_format
228
229           The current generated method used by "format_timestamptz",
230           "format_timestamp_with_time_zone", and "current_timestamp_parser"
231           to keep track of the "strptime" translation of
232           "NLS_TIMESTAMP_FORMAT".
233
234       •   oracle_to_posix
235
236           Given an "NLS_DATE_FORMAT", "NLS_TIMESTAMP_FORMAT", or
237           "NLS_TIMESTAMP_TZ_FORMAT" value, this method returns a
238           "DateTime"-compatible "strptime" format value.
239
240           Translation is currently handled by "Convert::NLS_DATE_FORMAT".
241

LIMITATIONS

243       Oracle is more flexible with the case of names, such as the month,
244       whereas "DateTime" generally returns names in "ucfirst" format.
245
246         MONTH -> FEBRUARY
247         Month -> February
248         month -> february
249
250       All translate to:
251
252         %B    -> February
253
254   TIME ZONES
255       Oracle returns all dates and timestamps in a time zone similar to the
256       "DateTime" floating time zone, except for 'timestamp with time zone'
257       columns.
258
259   INTERVAL ELEMENTS
260       I have not implemented "parse_duration", "format_duration",
261       "parse_interval", nor "format_interval", and have no plans to do so.
262
263       If you need these features, unit tests, method implementations, and
264       pointers to documentation are all welcome.
265

SUPPORT

267       Support for this module is provided via the datetime@perl.org email
268       list.  See http://lists.perl.org/ for more details.
269

TODO

271       Possibly read an environment variable to determine a time zone to use
272       instead of 'floating'.
273
274       Test and document creating an instance via "new".
275

AUTHOR

277       Nathan Gray, <kolibrie@cpan.org>
278

ACKNOWLEDGEMENTS

280       I might have put this module off for another couple years without the
281       lure of Jifty, Catalyst, and DBIx::Class pulling at me.
282
283       Thanks to Dan Horne for his RFC draft of this module.
284

COPYRIGHT & LICENSE

286       Copyright (C) 2006, 2008, 2011 Nathan Gray.
287
288       This library is free software; you can redistribute it and/or modify it
289       under the same terms as Perl itself, either Perl version 5.8.4 or, at
290       your option, any later version of Perl 5 you may have available.
291