sdo_rdf_triple_s

NLS quirks when loading triples into oracle semantic models

Something really strange happened during my latest tests against the bulk loading feature of oracle semantic technology. I was working through the demos, supplied with the oracle 11gr2 examples media, that is $ORACLE_HOME/md/demo/network/rdf_demos/bulkload{n}.* and other files, as the following error was raised when executing the load from the staging table. Obviously, oracle seems to complain about an invalid dateTime format, but, however, the more I rechecked the actual object value of the triple against the XMLSchema standard, in particular ISO 8601 for date and time datatypes, the more I learned that the given value was as correct as can be (note by the way, that there are in fact triples with the rdf demos that are erroneous by intent to debug the code in bulkload.ctl).

begin sem_apis.bulk_load_from_staging_table('yago2', 'lucene', 'yago2_stage'); end;
/
Line 117: ORA-13199: During LBV:ORA-13199: Element Parse Error: Invalid date/time value [debug info: GCVN-timestamp: 2030-10-20T12:10:00Z] (value: "2030-10-20T12:10:00Z"^^<http://www.w3.org/2001/XMLSchema#dateTime>) ORA-06512: in "MDSYS.MD", Zeile 1723
ORA-06512: in "MDSYS.MDERR", Zeile 17
ORA-06512: in "MDSYS.SDO_RDF", Zeile 9
ORA-06512: in "MDSYS.SDO_RDF_INTERNAL", Zeile 768

In order to minimize the problem space, I executed a simple insert statement using SDO_RDF_TRIPLE_S but only achieved the same result. Searching the net for comparable use cases I more and more sensed finding myself caught in an NLS-related problem. To prove this idea I executed another simple insert statement carrying an actual object value of XMLSchema#double as follows:

INSERT INTO yago2_data VALUES (null, SDO_RDF_TRIPLE_S ('yago2', '<http://example.org/Nathan>',
  '<http://example.org/age>', '"1.2"^^<http://www.w3.org/2001/XMLSchema#double>'))
/
Line 65: ORA-55303: SDO_RDF_TRIPLE_S-Konstruktor nicht erfolgreich: BNode-non-reuse case: SQLERRM=ORA-55328: Versuch, den literalen Wert "1.2"^^<http://www.w3.org/2001/XMLSchema#double> einzufügen, war nicht erfolgreich
ORA-06512: in "MDSYS.MD", Zeile 1723
ORA-06512: in "MDSYS.MDERR", Zeile 17
ORA-06512: in "MDSYS.SDO_RDF_TRIPLE_S", Zeile 64

Another ora-code but essentially the same outcome, uuhh? Ok, since my database sessions usually suppose a german NLS environment, I tried to replace the number separator from “.” to “,” and finally succeeded.

So I ask myself, what the hell is this? The interpretation of XMLSchema datatypes being dependent on the nls settings of the session environment. Hey guys @ oracle, did you ever read the XMLSchema standard (#!%&§$)?

Well, the appropriate settings that will resolve this loading quirk go like this:

ALTER SESSION SET NLS_DATE_LANGUAGE = American;
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' ;

have fun (nevertheless)!

Installing semantic technologies support on a fresh Oracle 11gR2.0.1

The following sketches the prerequisites of using Oracle Database Semantic Technologies, see Semantic Technologies Center and Oracle® Database Semantic Technologies Developer’s Guide as an overview and as the developer’s guide respectively, in your databse applications.

The procedure has been executed on a fresh Oracle 11R2.0.1 running on x64 Windows. The semantic technology stack in fact requires an enterprise edition of Oracle, featuring the partitioning and the spatial options as well as the underlying components XDB, JAVAVM and ORDIM to be installed … and licenced. Do check in advance whether this basement is affordable against your business budget because according to the current Oracle global price list by processor, an enterprise edition is around k$ 47, adding partioning by k$ 11 and spatial by k$ 17 plus updates and support. Do note also, that the standard and the standard one editions of Oracle, offering a subset of spatial called Oracle locator is not not sufficient to run the semantic technology layer. That is, it takes a couple of bucks to even create your first simple triple of sematic data with Oracle.

Checking the Oracle base software installation (anything executed as sysdba)

This section of the article, checking the Oracle base software installation itself, has been proof-taken from Stanley Guan’s blog Xml and More, entry Installation of Oracle Semantic Technologies. I can’t imagine why he has skipped the actual steps involving sematic-related code and objects there, moving me to write another post. However, Overview of Oracle Database Semantic Technologies is also worth a look really. 

-- verify the underlying components, namely XDB, JAVAVM and ORDIM
select comp_id,version,status from dba_registry where comp_id in ('JAVAVM','ORDIM','XDB');

COMP_ID  VERSION      STATUS
-------- ----------   -------
ORDIM    11.2.0.1.0   VALID
XDB      11.2.0.1.0   VALID
JAVAVM   11.2.0.1.0   VALID

-- assure the options partitioning and spatial are available
select parameter, VALUE from v$option where parameter in ('Partitioning', 'Spatial') order by 1;

PARAMETER     VALUE
------------  ------
Partitioning  TRUE
Spatial       TRUE

-- additionally check spatial in detail
set serveroutput on
execute validate_sdo;

select comp_id, control, schema, version, status, comp_name from dba_registry where comp_id='SDO';

COMP_ID   CONTROL   SCHEMA   VERSION      STATUS   COMP_NAME
--------  --------  -------  -----------  -------  ----------
SDO       SYS       MDSYS    11.2.0.1.0   VALID    Spatial

select object_name, object_type, status from dba_objects where owner='MDSYS' and status <> 'VALID';

Es wurden keine Zeilen ausgewõhlt

(more…)