Datatombraider's Blog

2010/11/07

Fun with dates and dbms_profiler

Filed under: Oracle — datatombraider @ 13:24

Some time ago i came across an issue with the dbms_profiler package. The package records unit timestamps in a table called PLSQL_PROFILER_UNITS. The problem is, for anonymous packages, which don’t have a timestamp, the package somehow manages to insert invalid dates:

UNIT_TIMESTAMP       UNIT_TIMESTAMP_CHAR  UNIT_TIMESTAMP_DUMP
——————– ——————– —————————————-
00-DECEMBER-00       0000-00-00 00:00:00  Typ=12 Len=7: 0,0,0,0,0,0,0

From the documentation: “unit_timestamp   DATE   In the future will be used to detect changes to unit between runs” but i would expect either no values or valid values. The funny thing is, that to_char() happily converts the bogus date to ‘0000-00-00 00:00:00’ but, at least, to_date() fails to convert it back to a date (“ORA-1843: not a valid month”).

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.