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.