Semantics in Oracle 11g R2

17 May 2011 by martin

I have been researching the technologies behind the Semantic Web for a rather interesting project. Having read as much as I could (none of it great), I manged to created a RDF model and a very basic ontology to do some experiments using SPARQL and the Jena tool kit. I really struggled to get the inferencing to work on Jena so I thought I'd give it a try in Oracle instead.

I set up my semantic model in Oracle as per the manual, having previously used SEM_APIS.CREATE_SEM_NETWORK to add RDF support to my database.

SQL> create table address_book_rdf_data(id number, triple sdo_rdf_triple_s);

SQL> execute sem_apis.create_sem_model('address_book', 'address_book_rdf_data', 'triple');

Next I tried to load model I created earlier into Oracle using the Oracle Java Bulk Loader. First problem was the Oracle RDF bulk loader only supports data files in N-Triple format. I created my model in Notation-3 (N3) format as I find it the easiest format to actually read. A quick google brought up this useful site for converting between RDF formats.

http://www.mindswap.org/2002/rdfconvert/

I created a small script to invoke the bulk loader.

cp=${ORACLE_HOME}/md/jlib/sdordf.jar:${ORACLE_HOME}/jdbc/lib/ojdbc5.jar
db='-Ddb.user=ab -Ddb.password=ab -Ddb.host=localhost -Ddb.port=1521 -Ddb.sid=orcl'
java $db -classpath $cp oracle.spatial.rdf.client.BatchLoader contacts.nt ADDRESS_BOOK_RDF_DATA RDF_USERS address_book

I found that if the loader failed for any reason, such as invalid username, it leaves behind a temporary table that prevents it running subsequently. To clear the temporary table you have to log in as a priviledged user (system in my case) and run this piece of SQL.

SQL> drop table mdsys.RDF_LINK$_TEMPBM$;

Eventually I got the bulk loader to partially run before it stopped with the following error:

$ sh load.sh
Note: You can use JVM properties -Ddb.user= -Ddb.password=
-Ddb.host= -Ddb.sid= -Ddb.port=
to configure database connection.
JVM property -Dappend=true can be used to specify APPEND mode. By default, this option is false.
JVM property -Dexchange=true can be used to allow table partition exchange which is faster and less space consuming.
This option is less robust though. By default, it is false

Connecting to jdbc:oracle:thin:@localhost:1521:orcl as user ab
Insert mode
Load triples into one column and populate another ID column
Batch loading started...
Subject:
Property:
Object: "+447xxxxxxxx0"

Loading up to rows = 69
java.sql.SQLException: ORA-55315: batch load attempt failed: SQLERRM=ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION ORA-06512: at "MDSYS.SDO_RDF_INTERNAL", line 5929
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.SDO_RDF_INTERNAL", line 5950
ORA-06512: at "MDSYS.SDO_RDF_INTERNAL", line 5960
ORA-06512: at "MDSYS.RDF_APIS", line 1283
ORA-06512: at line 1

 

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4693)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1086)
at oracle.spatial.rdf.client.NTripleConverter.loadNTriple(NTripleConverter.java:648)
at oracle.spatial.rdf.client.BatchLoader.main(BatchLoader.java:302)

I have put a question on the OTN discussion forum, if I get a response I'll update this post.

Update:  This is the response I received.

Hi, That loader (oracle.spatial.rdf.client.NTripleConverter) is a bit old. Please use the loader provided in Jena Adapter. You can refer to Example 7-3 of the following document. You may want to use addInBulk. http://download.oracle.com/docs/cd/E18283_01/appdev.112/e11828/sem_jena.htm#CBBCIJAD
Thanks, Zhe

So I followed that advice and it worked a treat!

One more thing...

Whilst doing this work with the Oracle database, I discovered a nice little trick in SQLPlus on Linux. On Windows it is very nice to be able to use your keyboard arrow keys to go back through your SQLPlus command history and make edits. On Linux this does not work by default. I found a few blogs that suggested using rlwrap to invoke sqlplus. I quickly installed rlwrap using yum and set up an alias in my .bashrc, it works perfectly.

alias sqlplus='rlwrap sqlplus'