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