One of the new features which seem to be overlooked in all the publications I saw about 18c is the TO_DOG_YEAR() function. It seems obvious that this was missed, because it fairly undocumented as Frank PachotPieter Van PuymbroeckBrendan TierneyMartin BergerOyvind Isene pointed out.
I wanted to know how it behaved on the Exadata especially on my customers OVM on Exadata. tested version : Exadata 18.1.4.0.0 my first tries where not successfully and still are not successful i see quite some strange behaviour.
desc dogyear
FUNCTION TO_DOG_YEAR RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DOB DATE IN
FEMALE BOOLEAN IN DEFAULT
NLS_BREAD VARCHAR2 IN DEFAULT
OWN_SMOKE BOOLEAN IN DEFAULT
i tried following :
select to_dogyear(to_date(‘01-04-2008','DD-MM-YYYY’) , ‘LABRADOR’) from dual ;
and this raised following :
ORA-07445: exception encountered: core dump [DOG_BREED_VIOLATION] [+34] [PC:0xDEADBEEF] [ADDR:0x14] [UNABLE_TO_BARK] []
when choosing another breed it worked although it gave a pretty bizarre result
select to_dogyear(to_date('28-03-2013','DD-MM-YYYY') , ‘POODLE’) a from dual ;
a
-------------------------
vulnerability not found
while according to pedigree it should be 50 and not only that it should RETURN a Number ? WTH ok what’s happen when we try to run the function on cats
select to_dogyear(to_date(‘01-04-2008','DD-MM-YYYY’), ‘GARFIELD’) a from dual ;
a
------------------------
is this a dog ?
Oracle you have some work to do. I would expect a number to be returned not a string Does anybody else with an Exadata see this behaviour preferably Bare Metal ? Cloud ?
Update 2-APR-2018 Before you think that PIO is Pets In Oracle A little update for those who didn’t realize this was posted on 1st of April. It was an April Fool common idea from some Oracle Community buddies on the post-UKOUG_TECH17 trip. And what remains true all the year is how this community is full of awesome people. And special thanks to Connor who added great ideas here 🙂
