How to solve CLOB column in Oracle?

[yize]

Hi ontop4obda group,

    I'm using ontop to extract data from Oracle. Here are commands I use:

    ontop bootstrap -b http://knowledge.microsoft.com/mso/ -p oracle.properties -t oracle_ontology.ttl -m oracle_mapping.obda

ontop mapping to-r2rml -i oracle_mapping.obda -o oracle_mapping.ttl -p oracle.properties -t oracle_ontology.ttl

ontop materialize -f ntriples -p oracle.properties -m oracle_mapping.ttl -o oracle_output.nt

But when there's column of 'CLOB' datatype in Oracle DB, "ontop materialize" throws exception: "inconsistency datatype, should be —, but get CLOB".

The reason is that ontop automatically generates sql query 'SELECT DISTINCT XXX, {a CLOB column}, XXX......' while CLOB column cannot work with 'DISTINCT'.

A complicated method we think of is to get the data structure of the database and delete the CLOB column from the oracle_ontology.ttl and oracle_mapping.obda files. Then continue with the subsequent command line.

Is there any other better way? Is the db-metadata file useful (I'm sorry I didn't find a detailed description about it)?

Thanks,

yize

[Thomas Taylor]

Hi,

I'm experiencing  a similar error message with a BLOB in Oracle. Ontop:5.1.0-SNAPSHOT "ORA-00932: inconsistent datatypes: expected - got BLOB"

https://github.com/ontop/ontop/issues/488 - It looks like this is fixed for CLOB -- I have another table with a CLOB that I can query correctly.

Should BLOB queries in Oracle work? 

Thanks,

Thomas

My r2rml maps Oracle's BLOB to hexBinary (SAKILA.STAFF.PICTURE)

-- SAKILA.STAFF definition

CREATE TABLE "SAKILA"."STAFF"

( "STAFF_ID" NUMBER(*,0) NOT NULL ENABLE,

"FIRST_NAME" VARCHAR2(45) NOT NULL ENABLE,

"LAST_NAME" VARCHAR2(45) NOT NULL ENABLE,

"ADDRESS_ID" NUMBER(*,0) NOT NULL ENABLE,

"PICTURE" BLOB DEFAULT NULL,

"EMAIL" VARCHAR2(50) DEFAULT NULL,

"STORE_ID" NUMBER(*,0) NOT NULL ENABLE,

"ACTIVE" NUMBER(*,0) DEFAULT 1 NOT NULL ENABLE,

"USERNAME" VARCHAR2(16) NOT NULL ENABLE,

"PASSWORD" VARCHAR2(40) DEFAULT NULL,

"LAST_UPDATE" DATE NOT NULL ENABLE,

CONSTRAINT "PK_STAFF" PRIMARY KEY ("STAFF_ID")

-- R2RML Mapping

_:genid-bc002d923440445081e4ccc44c7c8c7879-blank-194 rr:graph rr:defaultGraph;
  rr:predicate <rdb:/XE/SAKILA/STAFF#PICTURE>;

  rr:objectMap _:genid-bc002d923440445081e4ccc44c7c8c7879-blank-195 .

_:genid-bc002d923440445081e4ccc44c7c8c7879-blank-195 rr:column "\"PICTURE\"";
  rr:datatype <http://www.w3.org/2001/XMLSchema#hexBinary> .

ORA-00932: inconsistent datatypes: expected - got BLOB

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:122) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:968) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1270) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1148) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1660) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1469) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:2054) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:393) ~[ojdbc11-21.8.0.0.jar:21.8.0.0.0]
        at jdk.internal.reflect.GeneratedMethodAccessor59.invoke(Unknown Source) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[?:?]
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc-10.0.0-M7.jar:?]
        at com.sun.proxy.$Proxy156.executeQuery(Unknown Source) ~[?:?]
        at it.unibz.inf.ontop.answering.connection.impl.SQLQuestStatement.executeSelectQuery(SQLQuestStatement.java:196) ~[ontop-system-sql-core-5.1.0-SNAPSHOT.jar:5.1.0-SNAPSHOT]
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement.executeSelectQuery(QuestStatement.java:125) ~[ontop-system-core-5.1.0-SNAPSHOT.jar:5.1.0-SNAPSHOT]
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement.executeSelectQuery(QuestStatement.java:117) ~[ontop-system-core-5.1.0-SNAPSHOT.jar:5.1.0-SNAPSHOT]
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement$QueryExecutionThread.run(QuestStatement.java:100) ~[ontop-system-core-5.1.0-SNAPSHOT.jar:5.1.0-SNAPSHOT]