By Franck Pachot
At each new Oracle version, I like to check what’s new, not only from the documentation, but also from exposed internals. I look (and sometimes diff) on catalog views definitions, undocumented parameters, and even the new C functions in the libraries. At last Oak Table World, I was intrigued by this V$SQLFN_METADATA view explained by Vit Spinka when digging into the internals of how execution plans are stored. This view has entries with all SQL functions, and a VERSION column going from ‘V6 Oracle’ to ‘V11R1 Oracle’. The lastest functions has an ‘INVALID’ entry and we also can see some functions with ‘SQL/DS’. Well, now that we have Oracle 18c on the Oracle Cloud, I came back to this view to see if anything is new, listing the highest FUNC_ID at the top and the first row attired my attention:
SQL> select * from V$SQLFN_METADATA order by 1 desc fetch first 10 rows only; FUNC_ID NAME MINARGS MAXARGS DATATYPE VERSION ANALYTIC AGGREGATE OFFLOADABLE DISP_TYPE USAGE DESCR CON_ID ------- ---- ------- ------- -------- ---------- -------- --------- ----------- --------- ----- ----- ------ 1148 TO_DOG_YEAR 1 4 NUMERIC V13 Oracle NO NO YES NORMAL TO_DOG_YEAR 0 1147 JSON_MERGEPATCH 4 0 UNKNOWN INVALID NO NO NO NORMAL JSON_MERGEPATCH 0 1146 JSON_PATCH 4 0 UNKNOWN INVALID NO NO NO NORMAL JSON_PATCH 0 1145 ROUND_TIES_TO_EVEN 1 2 NUMERIC INVALID NO NO YES NORMAL ROUND_TIES_TO_EVEN 0 1144 CON_ID_TO_CON_NAME 1 0 UNKNOWN INVALID NO NO NO NORMAL CON_ID_TO_CON_NAME 0 1143 TIMESTAMP_TO_NUMBER 1 1 UNKNOWN INVALID NO NO YES NORMAL TIMESTAMP_TO_NUMBER 0 1142 TO_UTC_TIMESTAMP_TZ 1 0 UNKNOWN INVALID NO NO YES NORMAL TO_UTC_TIMESTAMP_TZ 0 1141 OPTSYSAPPROXRANK 1 0 UNKNOWN INVALID NO NO NO NORMAL Internal evaluation function for multiple approx_rank's 0 1140 APPROX_RANK 1 1 NUMERIC INVALID NO YES NO NORMAL APPROX_RANK 0 1139 APPROX_SUM 1 2 NUMERIC INVALID NO YES NO NORMAL APPROX_SUM 0
Because those functions are SQL functions, I searched this ‘TO_DOG_YEAR’ on Google to see whether a new ANSI SQL function was implemented. But finally came upon something I didn’t expect: Dog Years Calculator. The trends in databases are really going crazy these times. All focus is on developers. XML, JSON, Docker… and now a function to calculate your age in dog years.
But afterall, it makes sense. IoT (not ‘Index Organized Table’ but ‘Internet Of Things’) is coming with sensors everywhere. And it is not only ‘things’ but it comes to living beings. I have read recently about ‘Internet of Pets’ where collars equipped with sensors detect where your domestic animal go and when he is hungry.
Let’s test it. Tomorrow, my elder kid has his 13th birthday. Now Oracle can tell me that he will be 65 in dog years:
SQL> select to_dog_year(date'2005-04-02') from dual; TO_DOG_YEAR(DATE'2005-04-02') ----------------------------- 65
Yes, here I learn that the calculation is a bit more complex than just multiplying by 7. Of course, adding a SQL standard function would not make sense if it was just a multiplication.
But it seems to be even more complex. I searched for the C functions behind this one:
[[email protected] ~]$ nm /u01/app/oracle/product/18.0.0/dbhome_1/bin/oracle | grep -iE "dog.*year" 000000001452e073 r KNCLG_TODOGYEAR 0000000003ffcf40 T LdiJDaysDogYear 000000000f3170c0 T LdiJulianDogYear 000000000f316fc0 T LdiJulianDogYeararr 000000000f3170f0 t LdiJulianDogYeari 000000000f606e10 T OCIPConvertDateDogYearTime 000000000ebf2380 t qerxjConvertDogYearTime 0000000010de19e0 t qjsngConvStructDogYear 0000000010de0320 T qjsngNumberDogYearDty 0000000010de06f0 T sageStringDogYearDty 0000000010de7110 T sageplsDogYear 000000000bc5cd80 t sagerwAddDogYearTime 0000000010bad3c0 T qmxtgrConvSaxDogYear 0000000010bad400 T qmxtgrConvSaxDogYear_internal 00000000025ae090 T qosDateTimeDogYear 0000000004f22b60 T xsCHDogYeartime 000000000438c230 T nlsBreedDogYear 000000000438bb50 t nlsBreedDogYearCmn 000000000438c060 T nlsBreedDogYearTime 000000000438bc50 T nlsBreedDogYear 00000000044d1da0 T xvopAddDTDurDogYear 00000000044d1ac0 T xvopAddYMDurDogYear
Those ‘nlsBreed’ functions ring a bell and I checked if there are new values in V$NLS_VALID_VALUES
SQL> select distinct parameter from V$NLS_VALID_VALUES; PARAMETER ---------------------------------------------------------------- TERRITORY CHARACTERSET BREED LANGUAGE SORT
That ‘BREED’ is a new one, with a lot of interesting values:
And here is my example using this new NLS parameter.
SQL> select to_dog_year(date'2005-04-02','','NLS_BREED=Saint Bernard') from dual; TO_DOG_YEAR(DATE'2005-04-02', ---------------------------- 96
Note that I’ve no idea about the second parameter, I had to put a ‘null’ for it to be able to mention the NLS one, or I got a ‘ORA-00909: invalid number of arguments’.
Added a few hours later
There are a lot of questions about this new function. Here are some links to go further as many people in the Oracle Community have analyzed it further:
Martin Berger tested performance: http://berxblog.blogspot.ch/2018/04/more-fun-with-ages.html
Pieter Van Puymbroeck realized it was offloaded in Exadata: http://vanpupi.stepi.net/2018/04/01/exploring-18c-exadata-functions/
Brendan Thierney reveald a project he worked on in beta: http://www.oralytics.com/2018/04/predicting-ibs-in-dogs-using-oracle-18c.html
Øyvind Isene provides a way to test it with a cloud discount: http://oisene.blogspot.ch/2018/04/oracle-is-best-database-for-your-pets.html
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 🙂