Search code examples
plsqloracle11grdforacle-spatial

Exception thrown by `Insert all` RDF triples in Oracle11g

After creating a new table containing rdf data

create table rdf_data (triple SDO_RDF_TRIPLE_S);
execute SEM_APIS.CREATE_SEM_MODEL('mymodel', 'rdf_data', 'triple');

I am trying to add multiple RDF triples at once

insert all 
into rdf_data(triple) 
values (sdo_rdf_triple_s('mymodel', 'foo', 'bar', 'foobar'))
into rdf_data(triple) 
values (sdo_rdf_triple_s('mymodel', 'bar', 'foo', 'foobar')) 
select 1 from dual;

However, I am getting the following error:

ORA-00932:  inconsistent datatypes: expected NUMBER got BINARY
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

Instead, the following examples works:

-- Trying insert all with integers
create table foo(a integer);
insert all 
into foo values (1) 
into foo values (2) 
select 1 from dual;

-- Inserting a single RDF triple
insert into rdf_data(triple) 
values (sdo_rdf_triple_s('mymodel', 'foo', 'bar', 'foobar'));

However, given a table of 1,000,000 tuples I need to perform many inserts for each tuple (for instance, one insert for each table attribute). I suppose that having multiple insertions is not a viable solution.

create table bar(id varchar(100), foo char(100), foobar char(100));
insert all
into rdf_data(triple) 
values (sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<rdfs:type>', '<myont:Bar>'))
into rdf_data(triple) 
values (sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<myont:hasFoo>', foo))
into rdf_data(triple) 
values (sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<myont:hasFooBar>', foobar)) 
into rdf_data(triple) values (...)
select * from bar;

Is there any known bug within the insertion of multiple triples with insert all?

Solution

  • I guess you could say it is a bug. Or at least a limitation when dealing with the SDO_TRIPLE_S object type ...

    Then again, why not use multiple inserts ?

    EDIT 1: based on clarifications by the OP.

    So the actual need is to generate multiple triples from a single input row. Sorry for missing that from the original question. That is definitely an appropriate use for the multi-insert statement. Unfortunately, that does not seem to work when the value list contains an SDO_RDF_TRIPLE_S object. The restriction is not with all objects (other object types work fine).

    But then the SDO_RDF_TRIPLE_S constructor does extensive work in checking the physical triple store for existing values. I imagine that this is what causes trouble ...

    EDIT 2: The original INSERT ALL syntax works fine as long as you insert more columns than just the triple proper.

    Let's redefine the triple table like this:

    create table rdf_data (id number,triple SDO_RDF_TRIPLE_S);

    And fill the new ID column in the insert, like this:

    insert all
into rdf_data(id, triple) 
values (id, sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<rdfs:type>', '<myont:Bar>'))
into rdf_data(id, triple) 
values (id, sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<myont:hasFoo>', foo))
into rdf_data(id, triple) 
values (id, sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<myont:hasFooBar>', foobar)) 
select * from bar;

12 rows created.

Elapsed: 00:00:00.10

    That operation completes successfully. I don't know why the multi-insert fails when only the triple object is inserted. We need to investigate that.

    EDIT 3: workaround: Use multiple inserts

    The other workaround is to use multiple successive INSERT ... SELECT, each one generating one of the kind of triples to produce from the input rows.

    Consider this is the input table with the source for triples:

    create table bar(id varchar(100), foo char(100), foobar char(100));
insert into bar (id, foo, foobar) values (1, 'F1', 'FB1');
insert into bar (id, foo, foobar) values (2, 'F2', 'FB2');
insert into bar (id, foo, foobar) values (3, 'F3', 'FB3');
insert into bar (id, foo, foobar) values (4, 'F4', 'FB4');
commit;

    Here are the inserts:

    -- Insert the "<rdfs:type>" triples
insert into rdf_data(triple) 
select sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<rdfs:type>', '<myont:Bar>')
from bar;

-- Insert the "<myont:hasFoo>" triples
insert into rdf_data(triple) 
select sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<myont:hasFoo>', foo)
from bar;

-- Insert the "<myont:hasFooBar>" triples
insert into rdf_data(triple) 
select sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<myont:hasFooBar>', foobar)
from bar;

commit;

    A variant can be the more efficient:

    insert /*+ append */ into rdf_data(triple) 
-- Insert the "<rdfs:type>" triples
select sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<rdfs:type>', '<myont:Bar>')
from bar
union all
-- Insert the "<myont:hasFoo>" triples
select sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<myont:hasFoo>', foo)
from bar
union all
-- Insert the "<myont:hasFooBar>" triples
select sdo_rdf_triple_s('mymodel', '<myont:'||id||'>', '<myont:hasFooBar>', foobar)
from bar;
commit;