10 GOTO enesi.no

I haven't updated this blog for a long time. If you find anything useful here, you should probably upgrade your database. I'll leave it as is, but these blog posts will be copied to my new site and new blog posts appear there as well. 

Check out https://enesi.no/ if you have nothing better to do.

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:

@OyvindIsene agreed to be my guinea pig for my @vagrantup files. That's what you get when going to a conference! ;) https://t.co/pJDisr4Mhp

— Gerald Venzl (@GeraldVenzl) March 24, 2017

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.

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.

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 short⁰, 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 usually¹ 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.

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:

• REGEXP_COUNT
• REGEXP_INSTR
• REGEXP_REPLACE
• REGEXP_SUBSTR

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;
/

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 ;-)