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

Sunday, October 16, 2016

Importing GPX files to Oracle database, part 2

The previous post showed how to import a file in GPX-format into a table that uses the XMLType. This post shows how to import the waypoints from the GPS logger into a table with the native SDO_GEOMETRY type.

First create  a table:

create table gps_log (
  log_time timestamp with time zone,
  waypoint sdo_geometry,
  src varchar2(20));


The datatype for log_time is chosen like shown because the type used in the XML-file (xsd:dateTime) maps to this, which makes importing the time data much easier. (Have a look at the previous post to see this mapping.)

Not strictly necessary at this point, but I want to add metadata about the spatial column (aka layer). This is done by doing an insert on a view that resides in the MDSYS-schema (with a public synonym added). Insert on the view works by instead-of triggers defined on the view, in case you wondered how this works:


insert into user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME,diminfo,srid)
  values ('GPS_LOG','WAYPOINT',
    sdo_dim_array(sdo_dim_element('Latitude',-90,90,3),
      sdo_dim_element('Longitude',-180,180,3)),
    8307);
commit;

If you don't want to write this SQL yourself, you can use a nice feature in SQL Developer. Bring up the menu for the table in the Connections window, select Spatial and then Update Spatial Metadata...:


Then you can add the information in the pop-up window:


You can verify that this actually worked with:

select * 
from user_sdo_geom_metadata;

The app I was using used the position from the mobile network sometimes. I guess there was problem with the GPS reception at times. Anyway, the position reported from the mobile network does not include elevation, so I decided to leave it out. This has an implication when choosing the spatial reference ID; 8307 is commonly used for waypoints given by latitude and longitude (and without elevation), see this chapter for details. Another detail is the chosen tolerance; the app reported that the accuracy was around 4 meters from the GPS, usually worse from the mobile network, so I figured 3 meters is OK. (For geodetic coordinate systems the unit for tolerance is meter.)

Inserting the data into the table is pretty easy by adapting the SQL code at the end of previous post:

insert into gps_log(log_time,waypoint,src) 
  SELECT  EXTRACTVALUE(VALUE(t), 'trkpt/time'),
    sdo_geometry(2001,
      8307,
      sdo_point_type(EXTRACTVALUE(VALUE(t), 'trkpt/@lat'),
       EXTRACTVALUE(VALUE(t), 'trkpt/@lon'),null),
      null,
      null),
    EXTRACTVALUE(value(t),'trkpt/src') 
 FROM GPX g,
      TABLE(XMLSequence(extract(g.OBJECT_VALUE,
        '/gpx/trk/trkseg/trkpt',
        'xmlns="http://www.topografix.com/GPX/1/0"'))) t;
commit;

The following creates a spatial index on the WAYPOINT-column, but before you execute this, make sure the user has the privilege CREATE SEQUENCE in addition to CREATE TABLE:


create index gps_log_si
on gps_log(waypoint) indextype is mdsys.spatial_index


The statement above will return an error stack including ORA-29855 and ORA-13203 if metadata on the column is missing in USER_SDO_GEOM_METADATA. Probably it is a good habit to add it right after the creation of the table as shown previously. Note, if the statement failed, it may have created the index object (although not a useful one), and you may need to drop the index before you try again.


Tuesday, October 11, 2016

OTN Appreciation Day: A database that is reliable


As suggested by Tim Hall, aka Oracle-Base.com,  this post is about my favourite feature in the Oracle world. My favourite product is the good old database, and my favourite feature is simply the fact that I don't lose data!

Never once have I lost data due to software errors, and after many hard landings the database always manages to come back online after wading through the redo logs and make sure the data remains consistent.

I have been working as a DBA at sites where some decided to do maintenance on their UPS, the SAN, the network, or whatever in peak hours, resulting in hard landings that many databases won't recover from.  The worst nightmare for a DBA is to lose data. Yes, we have backups, and we spend time learning disaster recovery. But nothing beats the relief when you initiate "startup" after a crash and Oracle after a little while responds with "Database opened". You may say I've been lucky; software errors that corrupt data do exists, but after thousands of databases and many years with Oracle Database administration, not losing data remains my favourite feature.

My second favourite is all the instrumentation in there, the ability to figure out what is going on when someone complains. The database instrumentation is frequently a good starting place, even when the fault is outside somewhere, like in a badly designed application in another layer.

Thank you, OTN, for the support, information, articles, easy downloads, the ACE program, events, and much more. Have a great Appreciation Day!

Sunday, October 2, 2016

Importing GPX files to Oracle database, part 1

Update 2016-10-16: Found and error in procedure, see below.

In preparation for a presentation on Oracle Spatial for DBAs I decided to import some GPS-data. I have an app called GPSLogger on my Android mobile that logs GPS data to a file. The format for this is GPS Exchange Format (GPX).  Since I spent some time understanding the XML side of this I decided to blog about it.

Note, there are two versions of the GPX schema, 1.0 and 1.1. My app uses the version 1.0, if you try to import a GPX file, have a look in it to verify which schema definition it uses (Look for xsi:schemaLocation in the head of the GPX file).

I followed the information from this blog post (that actually uses version 1.1) together with info from Oracle Support, and lots of googling.

The app can upload to Dropbox which makes it easy to transfer the file to the database server. In addition I needed the schema definition, which you can get from Topographix.com, you'll find the link to the XSD file in the GPX file as mentioned, in my case it was http://www.topografix.com/GPX/1/0/gpx.xsd

However, the file needs some adjustment in order to have Oracle parse values of type xsd:dateTime correctly. Open it in an editor and look for the following block:

<xsd:schema
        xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        xmlns:gpx="http://www.topografix.com/GPX/1/0"
        targetNamespace="http://www.topografix.com/GPX/1/0"
        elementFormDefault="qualified">

At the end of this,  add one line so it looks like this:


<xsd:schema
        xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        xmlns:gpx="http://www.topografix.com/GPX/1/0"
        targetNamespace="http://www.topografix.com/GPX/1/0"
        elementFormDefault="qualified"
        xmlns:xdb="http://xmlns.oracle.com/xdb>"

Then you have to search through the file and search for every definition that uses type "xsd:dateTime" and add:

 xdb:sqltype="TIMESTAMP WITH TIME ZONE"

so it looks like this:

xsd:element minoccurs="0" name="time" type="xsd:dateTime" 
xdb:sqltype="TIMESTAMP WITH TIME ZONE"

(Take away the line break, I added it to make it readable).
There are four entries, so this manual job does not take a long time. I transferred this file together with my GPX file to a directory on my server (/u01/app/oracle/load_dir). Here is the first part to get started, the first part is executed as SYSTEM:


create user oyvind identified by oracle 
default tablespace users temporary tablespace temp 
quota unlimited on users;

grant create session, create table, create view, create procedure to oyvind;
grant alter session to oyvind;
grant create type to oyvind;

create directory LOAD_DIR as '/u01/app/oracle/load_dir';
grant read, write on directory load_dir to oyvind;

As you can see, I did not granted XDBADMIN to this user.  Update 2016-10-16: The use of DBMS_XDB does indeed require being SYS, the owner of the package (XDB), or having one of the roles XDBADMIN / DBA. Since I did not want an ordinary user to have an admin role, I executed the first part that uses the DBMS_XDB package as SYSTEM. The function below was created in the schema of the ordinary user as well. I must have been experimenting with granting and revoking the XDBADMIN without noticing that the first part did nothing since the resource was already there.

The first step is to load the XML schema definition to the database. I copied the function getClobDocument from the mentioned blog, it is used to load files as CLOB:


create or replace function getClobDocument(
    p_directoryname In varchar2,
    p_filename      In varchar2,
    p_charset       In varchar2 default NULL)
    return          CLOB deterministic
is
  v_file          bfile := bfilename(p_directoryname,p_filename);
  v_charContent   CLOB := ' ';
  v_targetFile    bfile;
  v_lang_ctx      number := DBMS_LOB.default_lang_ctx;
  v_charset_id    number := 0;
  v_src_offset    number := 1 ;
  v_dst_offset    number := 1 ;
  v_warning       number;
begin
  if p_charset is not null then
    v_charset_id := NLS_CHARSET_ID(p_charset);
  end if;
  v_targetFile := v_file;
  DBMS_LOB.fileopen(v_targetFile, DBMS_LOB.file_readonly);
  DBMS_LOB.LOADCLOBFROMFILE(v_charContent, 
                            v_targetFile,
                            DBMS_LOB.getLength(v_targetFile), 
                            v_src_offset, 
                            v_dst_offset,
                            v_charset_id, 
                            v_lang_ctx,
                            v_warning);
  DBMS_LOB.fileclose(v_targetFile);
  return v_charContent;
end;
/


The following code worked for me to create resource:


declare
  schemaURL varchar2(256) := 'http://www.topografix.com/GPX/1/0/gpx.xsd';
  schemaDoc varchar2(30) := 'gpx.xsd';
  xmlSchema xmlType;
  res       boolean;
begin
  xmlSchema := XMLTYPE(getCLOBDocument('LOAD_DIR',schemaDoc,'UTF8'));
  if (dbms_xdb.existsResource('/home/' ||schemaDoc)) then
      dbms_xdb.deleteResource('/home/' ||schemaDoc);
  end if;
  res := dbms_xdb.createResource('/home/' || schemaDoc,xmlSchema);
  if res then
    dbms_output.put_Line('OK');
  else
    dbms_output.put_Line('NOK');
  end if;
end;
/

Make sure you turn on server output and get a proper message to indicate if it succeeded or not; no error message was returned when it failed.

The rest of this is executed as user oyvind (Update: also create the getClobDocument from above to this user). Next step is to register the schema, I used the following code:

declare
  schemaURL varchar2(256) := 'http://www.topografix.com/GPX/1/0/gpx.xsd';
  schemaDoc varchar2(30) := 'gpx.xsd';
begin
  dbms_xmlschema.registerSchema
  (
    schemaURL,
    XMLTYPE(getCLOBDocument('LOAD_DIR',schemaDoc,'AL32UTF8')),
    local => true,
    genTypes => true,
    genTables => false,
    enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
  );
end;
/

Use the following code to create a table to store the document as XMLType:


CREATE TABLE gpx
    OF XMLType (CHECK (XMLIsValid(object_value) = 1)) 
    XMLSCHEMA "http://www.topografix.com/GPX/1/0/gpx.xsd" ELEMENT "gpx";


Then, finally, you can load the GPX file with this:


INSERT INTO GPX 
VALUES (XMLTYPE(getCLOBDocument('LOAD_DIR','20161001.gpx')));
commit; 

To see some of the data, you can extract what you find interesting with:


 SELECT 
       to_number(EXTRACTVALUE(VALUE(t), 'trkpt/@lon'))  longitude,
       to_number(EXTRACTVALUE(VALUE(t), 'trkpt/@lat'))  latitude,
       TO_NUMBER(EXTRACTVALUE(VALUE(t), 'trkpt/ele'))   Elevation,
       EXTRACTVALUE(value(t),'trkpt/src') src
 FROM GPX g,
      TABLE(XMLSequence(extract(g.OBJECT_VALUE,'/gpx/trk/trkseg/trkpt','xmlns="http://www.topografix.com/GPX/1/0"'))) t;


This is an example of what comes out (it was a short walk):


LONGITUDE LATITUDE ELEVATION SRC
10.79831903 59.83451229 85 gps
10.80009538 59.83263633 170 gps
10.80033655 59.83156553 146 gps
10.8003321 59.8303762 network

In next post I plan to use the Spatial datatype to store these points. I may do another test with GPX version 1.1 in the mean time since that is the version used by my Garmin GPS.

Some tips if you need to clean up in case of errors;  if you have the recyclebin enabled, do a "purge recyclebin" after dropping the table with the XMLType. I used to following command to delete the schema:

purge recyclebin;
exec dbms_xmlschema.deleteschema(schemaurl => 'http://www.topografix.com/GPX/1/0/gpx.xsd');


Get in touch if you need a copy of the final gpx.xsd file.