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

Sunday, March 26, 2017

Too Easy for a Post: Install Oracle 12.2 with Vagrant

If you like the simplicity of Docker, but would like to continue with Virtualbox, then Vagrant is for you. After the conference in Dublin,  OUG Ireland, I got this challenge:

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

Adding Examples to a Docker Container with Oracle database 12.2

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:

docker cp linuxx64_12201_examples.zip ora12.2:/tmp

Also unzip a copy of the included response file locally:
unzip -j -d /tmp linuxx64_12201_examples.zip \
examples/response/demos_install.rsp 

Edit it and change the variables so they look like this:

UNIX_GROUP_NAME=oinstall
ORACLE_HOME=/opt/oracle/product/12.2.0.1/dbhome_1
ORACLE_BASE=/opt/oracle

Copy it to the container:
docker cp /tmp/demos_install.rsp ora12.2:/tmp

Connect to your container with a command similar to this (again, ora12.2 is the name of my container):

docker exec -ti ora12.2 /bin/bash

Inside the container, unzip the transferred zip file and start the installation with:

cd /tmp
unzip linuxx64_12201_examples.zip
cd examples
./runInstaller -silent -force -waitforcompletion \
-responsefile /tmp/demos_install.rsp  -ignoresysprereqs -ignoreprereq

You can now verify that you have more demos installed, for instance under $ORACLE_HOME/md/demo.

Of course you can achieve the same effect by adding a few lines to the Dockerfile, build, and run again. Something like this should do for the Dockerfile:

# Added install of examples 
ENV EXAMPLE_RSP="demos_install.rsp" \
    EXAMPLE_FILE="linuxx64_12201_examples.zip"
COPY $EXAMPLE_RSP $EXAMPLE_FILE /tmp/
RUN cd /tmp && \
  unzip linuxx64_12201_examples.zip && \
  cd examples && \
  ./runInstaller -silent -force -waitforcompletion \
  -responsefile /tmp/demos_install.rsp  -ignoresysprereqs -ignoreprereq

It turned out to run just as fast since the new build builds on the previous images, oh well.

Monday, February 20, 2017

Splitting a String into Elements

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:

declare
  l_elements_arr apex_application_global.vc_arr2;
  l_str varchar2(2000) := 'IPA***Stout***Porter***Pale Ale';
  l_sep varchar2(10) := '***';
begin
  l_elements_arr := apex_util.string_to_table(l_str, l_sep);
  for i in 1..l_elements_arr.count loop
    dbms_output.put_line(l_elements_arr(i));
  end loop;
  l_str := apex_util.table_to_string(l_elements_arr,'|');
  dbms_output.put_line(l_str);
end;
/

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.

Another demo inspired by this Oracle blog post:
declare 
  l_str varchar2(100) := 'I$$bought$$a$$3D printer$$this weekend';
  l_sep varchar2(10) := '$$';
  l_exp varchar2(200) := '[^' || l_sep || ']+' ;
begin
  for e in (select regexp_substr(l_str,l_exp, 1, level) str
    from dual
    connect by regexp_substr(l_str,l_exp, 1, level) is not null) loop
    dbms_output.put_line(e.str);
  end loop;
end;
/

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

ORA-1722 and Regular Expressions

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:


select longitude 
from data_import
where regexp_instr(longitude,'\d+.\d{7}') = 0;

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

Delete Cascade with Recursive PL/SQL

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:


create or replace procedure delete_cascade(
  p_table_name in user_tables.table_name%type) is
  l_pk user_constraints.constraint_name%type;
begin
  select constraint_name into l_pk
  from user_constraints
  where Constraint_Type='P'
  and table_name=upper(p_table_name);
  for c in (
  select table_name
  from user_constraints
  where R_CONSTRAINT_NAME=l_pk) loop
    delete_cascade(c.table_name);
  end loop;
  execute immediate 'delete from ' || upper(p_table_name);
exception when no_data_found then -- Tables without constraint
  execute immediate 'delete from ' || upper(p_table_name);;
end;
/




Sunday, October 23, 2016

Displaying Spatial Data in SQL Developer

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:

alter table nuclear add geom sdo_geometry;
update nuclear set geom=sdo_geometry(2001,
  8307,
  sdo_point_type(to_number(substr(location,instr(location,',')+1)),
    to_number(substr(location,1,instr(location,',')-1)),
    null),
  null,
  null);

Since I want to try out a spatial operator, I need to add metadata for the column (or layer) and add a spatial index:

insert into user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME,diminfo,srid)
  values ('NUCLEAR','GEOM',
    sdo_dim_array(sdo_dim_element('Longitude',-180,180,3),
    sdo_dim_element('Latitude',-90,90,3)),8307);
create index nuclear_si on nuclear(geom) indextype is mdsys.spatial_index
parameters ('layer_gtype=POINT');

The parameter is not necessary, but when you use this, Oracle can check that all new data has the same GTYPE and will speed up index creation. Not a big deal here, but useful to know for later.

With this in place I can search for active reactors within a distance of 1000km from my home:

select   name,country
  ,round(sdo_geom.sdo_distance(geom
    ,sdo_geometry(2001,8307,sdo_point_type(10.8000994,59.8301125,null)
      ,null,null)
  ,10, 'unit=km')) "Distance in km"
from nuclear
where sdo_within_distance(geom,
  sdo_geometry(2001,8307
    ,sdo_point_type(10.8000994,59.8301125,null),null,null)
    ,'distance=1000 unit=km') = 'TRUE'
  and active_reactors>0
order by 3;

NAME COUNTRY Distance in km
RINGHALS SWEDEN 297
FORSMARK SWEDEN 415
OSKARSHAMN SWEDEN 434
BARSEBECK SWEDEN 471
OLKILUOTO FINLAND 604
BRUNSBUETTEL GERMANY 666
BROKDORF GERMANY 672
STADE GERMANY 696
KRUEMMEL GERMANY 716
UNTERWESER GERMANY 726
EMSLAND GERMANY 848
LOVIISA FINLAND 865
GROHNDE GERMANY 872
TORNESS UNITED KINGDOM 891
HARTLEPOOL UNITED KINGDOM 924
CHAPELCROSS UNITED KINGDOM 995

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:

select geom 
from spatial.world_countries
where sdo_within_distance(geom,
  sdo_geometry(2001,8307
    ,sdo_point_type(10.8000994,59.8301125,null),null,null)
  ,'distance=1000 unit=km') = 'TRUE';

and repeat the procedure. I also made this simple query to get an SDO_GEOMETRY object for my own location:

select sdo_geometry(2001,8307,sdo_point_type(10.8000994,59.8301125,null),null,null) 
from dual;

After changing the order, titles, colors, and zoom, the result look like this:



Also note that I didn't make any comments about our neighbours ;-)