Sunday, April 1, 2018

to_dog_year on Exadata

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 Pachot Pieter Van Puymbroeck Brendan Tierney Martin Berger Oyvind 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 :)

No comments: