Øyvind Isene's Blog

Sunday, April 1, 2018

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: https://www.pedigree.com/dog-care/dog-age-calculator 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.

Not much of a challenge, of course, since he has done all the work, it is just "Download and Go". Just follow the instructions in the README part on the page linked to in his Tweet. I did this on my Mac at the airport (I found a downloaded copy of 12.2 after all). The only comment I could make is that the git command creates a directory (oracle-12.2-vagrant) and you copy the downloaded zip file (linuxx64_12201_database.zip) into that directory.

In the oracle-12.2-vagrant directory there is of course the Vagrantfile that you can edit if you want. In case you have a listener already running on port 1521, you can easily change the Vagrantfile so the port 1521 from the VM will be forwarded to another port. I actually just did vagrant up on my server at home when preparing for this post, and it failed because port 1521 was already in use. No problem, just edit Vagrantfile (line number 19), and repeat the command, nothing to clean up first.

Do check out this blog post that came out today: Oracle Linux Vagrant boxes by Mikael Sandström. He has been using Vagrant a lot and actually created a box for many versions of Oracle Linux. Which means that if you want to try an upgrade on one specific version of Oracle Linux you can use one of his boxes easily. There are some other useful tips on Vagrant in the post too.

I will continue to play around with both Docker and VirtualBox/Vagrant. Creating new lab environments has become very easy so we can focus on more important stuff than just installing software.

Sunday, March 5, 2017

Oracle Database 12.2 became available for download last week. This weekend I've been playing with Docker and created a container with it. The whole process is so easy, thanks to the work by Gerald Venzl at Oracle. You'll find all the information you need in his blog post. Though the post is for version 12.1.0.2, the dockerfiles have already been updated for 12.2.0.1, and you can download it from Github using the link he provides in the post.

It takes sometime to create a container (depending on your hardware, of course), so when I discovered that the examples / demos that are distributed in another file were not included, I decided to try to add it to the running container. It is easy, just download the linuxx64_12201_examples.zip file and copy it into the running container, which in my case is named ora12.2:

Monday, February 20, 2017

Every 4 months or so I need a simple way to split a string (VARCHAR2) into elements, where the elements are separated with some fixed value (a comma, a colon, or perhaps a longer string). Since my short-term memory is too short0, I figured I should make a reminder here. Of course, you'll find this on Stackoverflow as well.

There is this function in APEX, which is usually1 available for you in the database, even if you are not using APEX. Here is a short demo:

The variables for the string and the separator can of course be part of a procedure / function. Also not that the separator can have more than one character. I used three stars because beer is probably good for you.

Since the work is done by the SQL statement in the FOR-LOOP, you can easily use this in pure SQL as well.

0) Let's just say that my buffer cache has surrendered space to the library cache, because there is so much interesting stuff going on, and all that parsing takes space.
1) "Usually", because APEX is installed when creating the database, unless you deselect one of the standard components, which would, in almost all cases (that is, more often than what "usually" means), would be a mistake. If you regret your mistake, just download the latest version and install it. That will take less time than it takes to teach me write proper sentences with an adequate use of strange words, and commas.

Sunday, February 19, 2017

Yesterday I was importing some data I downloaded. When creating a new table I tried to convert two columns with latitude and longitude, stored as VARCHAR2, to numbers (in order to use them in the SDO_GEOMETRY constructor). The create table as select (CTAS) statement failed with ORA-01722. Problem is it does not tell you which line or what value is the offender. You may turn on some tracing with event 1722, but using regular expression is much easier:

This will show you rows that does match the format used in the to_number function. Which in this case was '99.9999999'. Turned out that some of the rows had "(blank)" in them... they were updated to NULL.

Oracle database has support for regular expressions in theses functions:

Wednesday, November 30, 2016

If you need to delete all rows in a table that has parent keys for other tables' foreign keys, and the foreign keys constraints have not been defined with "on delete cascade", you can do a recursive delete with the following simple procedure.

This is typically something you will do only in a test or development database, and not in production. As always, it is a good thing to understand this procedure before you execute it:

Sunday, October 23, 2016

There are many ways to display spatial data, but when exploring a new data set in SQL Developer I think the built in Map View is practical. For this demo I used a list of nuclear power stations in the world, found via http://freegisdata.rtwilson.com/. The Google Fusion Table can be exported to CSV format, and it includes the longitude and latitude which makes it easy to convert to SDO_GEOMETRY. After importing the file with SQL Developer into a table called NUCLEAR, I did this to add native Spatial data:

A table looks nice, but it sure would be more informative on a map. I added the GEOM column to the query (and removed the others since I don't need them, and also the ORDER BY). The result is this:

Bring up the menu on the result set (right-click or similar on one of the rows), and select Invoke Map View on result set:

You get something like this:

That is not phenomenal. I had this idea that there should be a map in here. Well, a map with countries and their borders are spatial objects in themselves, and since I had downloaded the data files for the book Pro Oracle Spatial for Oracle Database 11g, I could just display the countries within the same distance: