The Daily WTF: Curious Perversions in Information Technology
Welcome to TDWTF Forums Sign in | Join | Help
in Search

The dark side of the the date: the type 13

Last post 03-23-2008 4:05 PM by dextron. 1 replies.
Page 1 of 1 (2 items)
Sort Posts: Previous Next
  • 03-20-2008 7:51 AM

    • zenobe
    • Not Ranked
    • Joined on 03-03-2008
    • Posts 2

    The dark side of the the date: the type 13

    Well, if you dig in the Oracle documentation you'll discover that the built-in type code for "date" is 12.

    But you don't known yet the dark side of the date ... the undocumented built-in type code 13...

    CREATE TABLE test (dt DATE);

    CREATE OR REPLACE VIEW test_vw (dt) AS SELECT TRUNC(dt,'DD') FROM test;

    INSERT INTO test (DT) VALUES(TRUNC(SYSDATE,'DD'));

    SELECT 'test' source,dt, SUBSTR(DUMP (dt),1,40) dump_dt FROM test
    UNION
    SELECT 'test_vw' source,dt, SUBSTR(DUMP (dt),1,40) dump_dt FROM test_vw
    ;

    SOURCE DT DUMP_DT
    test 20-MAR-08 Typ=12 Len=7: 120,108,3,20,1,1,1
    test_vw 20-MAR-08 Typ=13 Len=8: 216,7,3,20,0,0,0,0
  • 03-23-2008 4:05 PM In reply to

    Re: The dark side of the the date: the type 13

    Oracle does not document External Datatype 13 because it is an internal C structure whose length varies according to implementations, is only present in date calculations, and not persisted to disk.  You are seeing this because you are dumping the result of a function call, not an internal datatype (type 12)  that would be writen to disk.  Check out metalink Note:69028.1 for more information. While I haven't tested it, I'm certain OCI will perform the conversion to 12 if you set the dty parameter accordingly. 

Page 1 of 1 (2 items)
Powered by Community Server (Non-Commercial Edition), by Telligent Systems