Exploring 18c exadata functions

Recently I was granted access to the finest and latest exadata. Unfortunately it’s not mine, but I was asked if I managed to log in remotely as a test. Of course I wanted to test that. As it turned out, it was the very latest build of 18c which is available for exadata. You can find the release schedules for Oracle versions here: the single-source-of-truth MOS Note: 742060.1 – Release Schedule of Current Database Releases. I realised this was a cool opportunity and asked the person if I could play with it a bit. During my presentations I often say “expect the unexpected” and I got even a personal sandbox database on the system. Yay!

Recently some colleagues in the Oracle Community pointed me to a very useful view to start exploring databases: v$sqlfn_metadata. This one contains metadata about operators and built-in functions. Note that this view does not contain information about arguments because the number of arguments will be different for various functions. Information about arguments is contained in V$SQLFN_ARG_METADATA. So let’s see what are the top-ten newest build-in functions:

1141OPTSYSAPPROXRANK10UNKNOWN INVALID NO NO NO NORMAL Internal evaluation functionformultiple approx_rank's0

1140APPROX_RANK11NUMERIC INVALID NO YES NO NORMAL APPROX_RANK0

1139APPROX_SUM12NUMERIC INVALID NO YES NO NORMAL APPROX_SUM0

10rows selected.

SQL>

Mmz, really? Are we serious? This can’t be true, so I did what I always do when I don’t believe something. Look it up. In this case, as I did not find things in the documentation, I used my friend google.

Apparantly I’m not the only one who is looking for this and it IS a thing it seems. I can imagine that it might come in handy for statistical things or so. As this is very remarkable, I want to see a little more from this.

First easy test is “does it work”? I pick just a random number.

1

2

3

4

5

6

7

SQL>select to_dog_year(to_date('28-03-2013','DD-MM-YYYY'))from dual;

TO_DOG_YEAR(to_date('28-03-2013','DD-MM-YYYY'))

------------------------------------------------

36.21

SQL>

Apparently it does 😀 and there is apparently some intelligence build in. I was expecting 35.
Any other parameters documented?

Anyway, I like that they are implementing the BOOLEAN datatype and this is also a positive shoutout to #WIT. They chose female as argument, which is in my opinion a nice thing.

Who will use this? I don’t know. Possible people interested in statistics, but it’s cool to see new features developed. I’m curious if it’s there in the cloud too.

As always, questions, remarks? find me on twitter @vanpupi

Update 2-APR-2018

For those who didn’t realize this was posted on 1st of April. This idea came 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 as well on asktom 🙂