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


  • 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. 


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.