The dark side of the the date: the type 13

[zenobe]

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

[dextron]

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.