Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Error Msg = ORA-00932: inconsistent datatypes: expected - got CLOB #488

Open
hpstory opened this issue Dec 13, 2021 · 4 comments
Open

Error Msg = ORA-00932: inconsistent datatypes: expected - got CLOB #488

hpstory opened this issue Dec 13, 2021 · 4 comments

Comments

@hpstory
Copy link

hpstory commented Dec 13, 2021

Description

i try to generate RDF data out of oracle use following commands, but got error in command 3.

1. ontop bootstrap -b http://www.example.org/ -p oracle.properties -t oracle_ontology.ttl -m oracle_mapping.obda

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

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

because some columns in table which data type is CLOB and get sql syntax error like

SELECT DISTINCT V1."VIOLA_ACTION" AS "VIOLA_ACTION1m1192"
FROM "VIOLATION_BASICINFO_JX" V1
WHERE (V1."VIOLA_ACTION" IS NOT NULL)
// VIOLA_ACTION data type is CLOB

Data type cannot be modified. Are there any other ways to deal with CLOB data type, or remove distinct(will it cause other errors?)

Thank for your help.

Actual behavior: [What actually happens]

The following is the error log

D:\> .\ontop.bat materialize -f ntriples -p oracle.properties -m oracle_mapping.ttl -o oracle_output.nt

15:01:27.534 |-INFO  in i.u.i.o.a.r.impl.QuestQueryProcessor - Ontop has completed the setup and it is ready for query answering!
15:03:24.753 |-ERROR in i.u.i.o.a.c.impl.QuestStatement - java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

it.unibz.inf.ontop.exception.OntopQueryEvaluationException: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

        at it.unibz.inf.ontop.answering.connection.impl.SQLQuestStatement.executeSelectQuery(SQLQuestStatement.java:205)
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement.executeSelectQuery(QuestStatement.java:122)
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement.executeSelectQuery(QuestStatement.java:114)
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement$QueryExecutionThread.run(QuestStatement.java:97)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:765)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1362)
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:369)
        at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
        at com.sun.proxy.$Proxy44.executeQuery(Unknown Source)
        at it.unibz.inf.ontop.answering.connection.impl.SQLQuestStatement.executeSelectQuery(SQLQuestStatement.java:197)
        ... 3 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00932: inconsistent datatypes: expected - got CLOB

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
        ... 21 common frames omitted
15:03:24.757 |-ERROR in i.u.i.o.m.i.DefaultMaterializedGraphResultSet - Problem materializing the class/property http://knowledge.microsoft.com/mso/VIOLATION_BASICINFO_JX#RELATED_LAW/2
java.lang.RuntimeException: org.eclipse.rdf4j.query.QueryEvaluationException: it.unibz.inf.ontop.exception.OntopQueryEvaluationException: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

        at it.unibz.inf.ontop.cli.OntopMaterialize.runWithSingleFile(OntopMaterialize.java:159)
        at it.unibz.inf.ontop.cli.OntopMaterialize.run(OntopMaterialize.java:109)
        at it.unibz.inf.ontop.cli.Ontop.main(Ontop.java:20)
Caused by: org.eclipse.rdf4j.query.QueryEvaluationException: it.unibz.inf.ontop.exception.OntopQueryEvaluationException: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

        at it.unibz.inf.ontop.rdf4j.materialization.impl.DefaultRDF4JMaterializer$GraphMaterializationIteration.hasNext(DefaultRDF4JMaterializer.java:249)
        at org.eclipse.rdf4j.common.iteration.IterationWrapper.hasNext(IterationWrapper.java:63)
        at it.unibz.inf.ontop.cli.OntopMaterialize.serializeTripleBatch(OntopMaterialize.java:242)
        at it.unibz.inf.ontop.cli.OntopMaterialize.runWithSingleFile(OntopMaterialize.java:152)
        ... 2 more
Caused by: it.unibz.inf.ontop.exception.OntopQueryEvaluationException: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

        at it.unibz.inf.ontop.answering.connection.impl.SQLQuestStatement.executeSelectQuery(SQLQuestStatement.java:205)
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement.executeSelectQuery(QuestStatement.java:122)
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement.executeSelectQuery(QuestStatement.java:114)
        at it.unibz.inf.ontop.answering.connection.impl.QuestStatement$QueryExecutionThread.run(QuestStatement.java:97)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:765)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1362)
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:369)
        at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
        at com.sun.proxy.$Proxy44.executeQuery(Unknown Source)
        at it.unibz.inf.ontop.answering.connection.impl.SQLQuestStatement.executeSelectQuery(SQLQuestStatement.java:197)
        ... 3 more
Caused by: Error : 932, Position : 527, Sql = SELECT DISTINCT V1."COMPANY_ID" AS "COMPANY_ID1m1192", V1."CREATE_DT" AS "CREATE_DT1m1192", V1."ID" AS "ID1m1192", V1."IS_DEL" AS "IS_DEL1m1192", V1."IS_VERIFIED" AS "IS_VERIFIED1m1192", V1."NOTICE_DT" AS "NOTICE_DT1m1192", V1."OPERATER" AS "OPERATER1m1192", V1."OPERATER_ID" AS "OPERATER_ID1m1192", V1."PUNISH_AMT" AS "PUNISH_AMT1m1192", V1."PUNISH_DT" AS "PUNISH_DT1m1192", V1."PUNISH_OBJECT" AS "PUNISH_OBJECT1m1192", V1."PUNISH_OBJECT_ID" AS "PUNISH_OBJECT_ID1m1192", V1."PUNISH_OBJECT_TYPE" AS "PUNISH_OBJECT_TYPE1m1192", V1."PUNISH_STEP" AS "PUNISH_STEP1m1192", V1."PUNISH_TITLE" AS "PUNISH_TITLE1m1192", V1."PUNISH_TYPE" AS "PUNISH_TYPE1m1192", V1."REF_NUMBER" AS "REF_NUMBER1m1192", V1."RELATED_LAW" AS "RELATED_LAW1m1192", V1."RELATION" AS "RELATION1m1192", V1."UPDT_DT" AS "UPDT_DT1m1192", V1."VIOLATION_BASICINFO_SID" AS "VIOLATION_BASICINFO_SID1m1192", V1."VIOLA_ACTION" AS "VIOLA_ACTION1m1192", V1."VIOLA_LTYPE" AS "VIOLA_LTYPE1m1192"
FROM "VIOLATION_BASICINFO_JX" V1
WHERE (V1."COMPANY_ID" IS NOT NULL AND V1."PUNISH_OBJECT_ID" IS NOT NULL AND V1."PUNISH_OBJECT" IS NOT NULL AND V1."PUNISH_OBJECT_TYPE" IS NOT NULL AND V1."RELATION" IS NOT NULL AND V1."NOTICE_DT" IS NOT NULL AND V1."PUNISH_DT" IS NOT NULL AND V1."PUNISH_TITLE" IS NOT NULL AND V1."VIOLA_LTYPE" IS NOT NULL AND V1."VIOLA_ACTION" IS NOT NULL AND V1."PUNISH_TYPE" IS NOT NULL AND V1."PUNISH_STEP" IS NOT NULL AND V1."OPERATER_ID" IS NOT NULL AND V1."OPERATER" IS NOT NULL AND V1."PUNISH_AMT" IS NOT NULL AND V1."RELATED_LAW" IS NOT NULL AND V1."REF_NUMBER" IS NOT NULL AND V1."IS_VERIFIED" IS NOT NULL AND V1."IS_DEL" IS NOT NULL)
, OriginalSql = SELECT DISTINCT V1."COMPANY_ID" AS "COMPANY_ID1m1192", V1."CREATE_DT" AS "CREATE_DT1m1192", V1."ID" AS "ID1m1192", V1."IS_DEL" AS "IS_DEL1m1192", V1."IS_VERIFIED" AS "IS_VERIFIED1m1192", V1."NOTICE_DT" AS "NOTICE_DT1m1192", V1."OPERATER" AS "OPERATER1m1192", V1."OPERATER_ID" AS "OPERATER_ID1m1192", V1."PUNISH_AMT" AS "PUNISH_AMT1m1192", V1."PUNISH_DT" AS "PUNISH_DT1m1192", V1."PUNISH_OBJECT" AS "PUNISH_OBJECT1m1192", V1."PUNISH_OBJECT_ID" AS "PUNISH_OBJECT_ID1m1192", V1."PUNISH_OBJECT_TYPE" AS "PUNISH_OBJECT_TYPE1m1192", V1."PUNISH_STEP" AS "PUNISH_STEP1m1192", V1."PUNISH_TITLE" AS "PUNISH_TITLE1m1192", V1."PUNISH_TYPE" AS "PUNISH_TYPE1m1192", V1."REF_NUMBER" AS "REF_NUMBER1m1192", V1."RELATED_LAW" AS "RELATED_LAW1m1192", V1."RELATION" AS "RELATION1m1192", V1."UPDT_DT" AS "UPDT_DT1m1192", V1."VIOLATION_BASICINFO_SID" AS "VIOLATION_BASICINFO_SID1m1192", V1."VIOLA_ACTION" AS "VIOLA_ACTION1m1192", V1."VIOLA_LTYPE" AS "VIOLA_LTYPE1m1192"
FROM "VIOLATION_BASICINFO_JX" V1
WHERE (V1."COMPANY_ID" IS NOT NULL AND V1."PUNISH_OBJECT_ID" IS NOT NULL AND V1."PUNISH_OBJECT" IS NOT NULL AND V1."PUNISH_OBJECT_TYPE" IS NOT NULL AND V1."RELATION" IS NOT NULL AND V1."NOTICE_DT" IS NOT NULL AND V1."PUNISH_DT" IS NOT NULL AND V1."PUNISH_TITLE" IS NOT NULL AND V1."VIOLA_LTYPE" IS NOT NULL AND V1."VIOLA_ACTION" IS NOT NULL AND V1."PUNISH_TYPE" IS NOT NULL AND V1."PUNISH_STEP" IS NOT NULL AND V1."OPERATER_ID" IS NOT NULL AND V1."OPERATER" IS NOT NULL AND V1."PUNISH_AMT" IS NOT NULL AND V1."RELATED_LAW" IS NOT NULL AND V1."REF_NUMBER" IS NOT NULL AND V1."IS_VERIFIED" IS NOT NULL AND V1."IS_DEL" IS NOT NULL)
, Error Msg = ORA-00932: inconsistent datatypes: expected - got CLOB

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
        ... 21 more
Exception in thread "main" java.lang.AssertionError
        at org.junit.Assert.fail(Assert.java:87)
        at org.junit.Assert.fail(Assert.java:96)
        at it.unibz.inf.ontop.cli.Ontop.main(Ontop.java:32)

Versions

ontop version 4.1.1
@hpstory
Copy link
Author

hpstory commented Dec 14, 2021

oracle.properties

jdbc.url = jdbc:oracle:thin:@host:port/server
jdbc.user = username
jdbc.password = 1234
jdbc.driver = oracle.jdbc.OracleDriver

@hpstory
Copy link
Author

hpstory commented Dec 14, 2021

By the way, how can i use metadata file，i got file by extract-db-metadata, and remove columns which datatype is CLOB in oracle_metadata.json, then i tried

ontop extract-db-metadata -o oracle_metadata.json -p oracle.properties
ontop bootstrap -b http://www.example.org/ -d oracle_metadata.json -p oracle.properties -t oracle_ontology.ttl -m oracle_mapping.obda

it seems doesn't work, the columns i removed from metadata file still exists in oracle_ontology.ttl and oracle_mapping.obda. What should i do, and how to use it correctly，Thank for your help.

@bcogrel
Copy link
Member

bcogrel commented Dec 14, 2021

Hi @hpstory, thanks for reporting this issue.

Your last test showed that the serialized DB metadata is not taken into account by the boostrapper, I will create an issue for that.

The DISTINCT in the SQL queries are most likely due to the absence of primary keys in some tables. Unfortunately at the moment, we don't have a way to ignore some tables or schemas when doing boostrapping, but there is an issue tracking this feature request (#471).

A common trick for avoiding this issue seems to be casting the CLOB into a VARCHAR (using TO_CHAR) in the presence of a DISTINCT. This only works if the string is not too large (4000 characters seem to be the limit). This in principle could be implemented.

@bcogrel bcogrel mentioned this issue Dec 14, 2021
Open
3 tasks
@hpstory
Copy link
Author

hpstory commented Dec 15, 2021

@bcogrel Thank you for your reply, I'm going to replace the contents in output files by code to avoid this issue, look forward to the follow-up progress.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@bcogrel @hpstory