Sunday, April 1, 2018

Oracle is the best database for your pets

The Oracle database is around 40 years old, but is frequently updated with new features to answer the demands from the IT industry, and the rest of the world of end users.

When object orientation (OO) (or disorientation some would say) was very hot it got supported in version 8. XML support is excellent if you need to exchange data through an API and CSV wouldn't be fancy enough. Now with all the teenagers writing stuff in JavaScript it was only a question of time before JSON support got introduced, which happened in 12c.

With JSON and younger generations we have seen more consciousness for the world and life around us. "Technology is fine, but what is important to me is my dog", said an aspiring JS programmer on Twitter recently. "I want a safe place to store data about my dog's health and analyze them so I can be sure he won't be suffering as he ages".

It turned out to be more challenging than he thought. A year in a dogs life is, as most of you calendar freaks know, not 365 days, and in fact the length of a dog year changes with age and depends on the breed.

One online resource for calculating the age of your dog can be found here: But for such critical calculations we should not rely on an online resource written in JavaScript with all the pitfalls for wrong calculations (of which I have heard there are many).

After a few in the Oracle Community picked up this issue, they contacted key resources in Oracle to ask for assistance on this, knowing that they can be quick to implement new features when the demand and business issue is concrete.

The function to calculate the age of your dog in dog years are now available in the latest patch of 18c. Since all truly caring dog owners will use a database in the cloud for their pet's data, the function is available in the Oracle Cloud, and possibly for on-prem databases later.

The function to_dog_year can be called like this:
select to_dog_year(12,FALSE,'American Bulldog') from dual;
Since breed is obviously static for your pet, Oracle has introduced a new NLS parameter for it, so you can set it in your session. First check v$nls_valid_values that the breed of your dog is correctly spelled:
select value
from v$nls_valid_values
where parameter='BREED';
Then set the parameter in your session with:
alter session set NLS_BREED='American Hairless Rat Terrier';
Note the combination of all valid values is almost complete, but if you cannot find your dog in the v$nls_valid_values table, please open a SR at Oracle Support and request an update. This has got a lot of attention at Oracle for the moment. The patch is one simple update that can be done online.

In the next post I will demonstrate how this function can be used in Advanced Analytics to detect health issues using anomaly detection. If you want to be prepared you should create an instance in the Oracle Cloud, make sure you select the latest patch level. For new users the code NLS_BREED will give a 12% discount.