Infrastructure at your Service

Franck Pachot

After IoT, IoP makes its way to the database

By April 1, 2018 Oracle 3 Comments

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;

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;

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;

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’.

I have to say that, for a DBA focused on the core database functions, it is hard to understand that new features go on things like this TO_DOG_YEAR function. But being realistic, it is clear that the budget for new features go into the new direction: all for developers, big data, IoT… Of course we can write those functions in PL/SQL or maybe one day with JavaScript thanks to the Multi-Lingual Engine currently in beta. But IoT is also about performance, and a standard function avoids context switches.

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:
Pieter Van Puymbroeck realized it was offloaded in Exadata:
Brendan Thierney reveald a project he worked on in beta:
Øyvind Isene provides a way to test it with a cloud discount:

Update 2-APR-2018

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 🙂


  • In addition to that, Martin Berger has tested the performance with different input:

  • Connor says:

    Hi Frank,

    You can contact me offline for details about the second parameter, but don’t forget that as an Ace Director, there may still be NDA issues you’ll need to aware of.

    Also, probably worth reminding readers that, similarly to timezone data, there will be regular updates to the breed metadata files. Cloud customers will get those updates are part of scheduled patch updates.

    Normal NLS language settings also come into play, to allow for equivalence between (for example), “Dachshund”, “Dachshound”, “Dackel” etc

    Connor McDonald

  • Marat says:

    Nice try Franck. Happy Fool’s Day.

    SQL> select banner from v$version;

    Oracle Database 18c Enterprise Edition Release – Production

    SQL> select * from v$sqlfn_metadata where name like ‘%DOG%’;

    no rows selected


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director
Oracle Database OCM 12c certified
AWS Database Specialty certified
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn :
Podcast en français: DBPod