Datatombraider's Blog


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:

——————– ——————– —————————————-
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”).


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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at