Exception when updating Oracle NVARCHAR2 columns

[ileitch]

Is there a workaround for this?

Thanks

Test SQL  = “Update Test set TestNVarCharCol=’TESTVARCHAR’”;

26-Oct-2012 1:36:51 PM org.teiid.logging.JavaLogger log

WARNING: Connector worker process failed for atomic-request=vKWKHHO7tamG.0.0.0

org.teiid.translator.jdbc.JDBCExecutionException: 17004 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: ['TESTVARCHAR'] SQL: UPDATE "TESTADMIN"."TEST" SET "TestNVarCharCol" = ?]

                at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:211)

                at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:71)

                at com.lgc.edm.teiid.translator.EdmUpdateExecution.execute(EdmUpdateExecution.java:34)

                at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:276)

                at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:241)

                at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:355)

                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:144)

                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:141)

                at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

                at java.util.concurrent.FutureTask.run(FutureTask.java:138)

                at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:116)

                at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:245)

                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123)

                at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298)

                at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)

                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)

                at java.lang.Thread.run(Thread.java:619)

Caused by: java.sql.SQLException: Invalid column type

                at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

                at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)

                at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)

                at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3088)

                at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3107)

                at org.teiid.translator.jdbc.JDBCExecutionFactory.bindValue(JDBCExecutionFactory.java:830)

                at org.teiid.translator.jdbc.oracle.OracleExecutionFactory.bindValue(OracleExecutionFactory.java:476)

                at org.teiid.translator.jdbc.JDBCBaseExecution.bind(JDBCBaseExecution.java:101)

                at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:203)

                ... 16 more

WARNING: TEIID30020 Processing exception 'TEIID30504 Edm: 17004 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: ['TESTVARCHAR'] SQL: UPDATE "TESTADMIN"."TEST" SET "TestNVarCharCol" = ?]' for request vKWKHHO7tamG.0.  Exception type org.teiid.core.TeiidProcessingException thrown from oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134). Enable more detailed logging to see the entire stacktrace.

[shawkins]

What version of the Oracle driver are you using?  Teiid will bind the value using setObject as a VARCHAR type.  It could be that driver version or the Oracle driver in general wants a different type argument for the parameter binding or needs more Oracle specific considerations like shown at - http://docs.oracle.com/cd/B19306_01/java.102/b14355/global.htm#CHDHHJDB

There oracle.jdbc.defaultNChar workaround shown there may be applicable, however we're not using the setString call so I can't say for certain without trying it out.

Steve

[ileitch]

Suggested Oracle JDBC property: -Doracle.jdbc.defaultNChar=true.

It still gives the same exception: Invalid Column Type

The TEIID jdbc driver returns SQLType of “JAVA_OBJECT” for NVARCHAR, so it throws and exception in Oracle JDBC call “setObject(i,param,TYPE.JAVA_OBJECT)”.

We can reproduce the same exception using the Oracle jdbc driver by making the same setObject call.

Thanks for your help!

Oracle JDBC version:

Manifest-Version: 1.0

Ant-Version: Apache Ant 1.6.5

Created-By: 1.5.0_24-rev-b08 (Sun Microsystems Inc.)

Implementation-Vendor: Oracle Corporation

Implementation-Title: JDBC

Implementation-Version: 11.2.0.2.0

Repository-Id: JAVAVM_11.2.0.2.0_LINUX_100827

Specification-Vendor: Sun Microsystems Inc.

Specification-Title: JDBC

Specification-Version: 4.0

Main-Class: oracle.jdbc.OracleDriver

sealed: true

Name: oracle/sql/converter/

Sealed: false

Name: oracle/sql/

Sealed: false

Name: oracle/sql/converter_xcharset/

Sealed: false

[shawkins]

Can you log an issue for this?  Also are you using a dynamic or a designer vdb?

[ileitch]

We are currently using embedded, but we had the same issue with a dynamic vdb defined in xml. Actually we have not tried designer yet.

We are wondering if this is related:

https://issues.jboss.org/browse/TEIIDDES-179

I can log an issue.

Thanks

[kchen007]

This issue seems to be fixed in Teiid 8.2, right now we are using Teiid 8.7 and the issue come back. The Oracle database has columns defined as NVARCAR2, we use Teiid Oracle translator, but the column is mapped to Object instead of String.

[shawkins]

You may be seeing a regression with [TEIID-2644] Support retrieving result sets from any out parameter - JBoss Issue Tracker in the importer type mapping it is looking for char in the type name, but that is case sensitive and your driver may be reporting the type name as NVARCHAR2.

[kchen007]

the mapping is from in org.teiid.translator.TypeFacility.getDataTypeNameFromSQLType(int sqlType), for the NVARCHAR2, the sql type is 1111. and NVARCHAR2 is not registered in map defined in JDBCSQLTypeInfo.
.

[shawkins]

There is a specific override in the Oracle translator.  In 8.7 see OracleExecutionFactory.getRuntimeType or in master OracleMetadataProcessor.getRuntimeType.  Note that the check for char that was introduced in TEIID-2644 - which was needed because oracle reports several data types as OTHER.

[kchen007]

I see, the data base report the name as capital NVARCHAR2, the check is with 'char'. Any way to work around it? thanks.

[shawkins]

Other than a code change, you would have to fully specify the DDL.

[shawkins]

Logged as [TEIID-3294] dynamic vdb imports nvarchar2 as object - JBoss Issue Tracker for 8.10

[kchen007]

thanks!

Kevin