Hi,
Version ODI: Build ODI_12.2.1.2.6_GENERIC_161202.0826
I have problem.
I have table on postgreSQL like layer.
It has column like: ID, DESC and others (but I for me this two columns is the most improtant).
ID is number, DESC is json (has more than 4000 length).
Next I create table on my oracle base: LAYER_1 with ID NUMBER and DESC clob.
And in ODI datastore for table:
LAYER from postresql i set ID - NUMERIC, DESC - VARCHAR
LAYER_1 from oracle: ID - NUMERIC, DESC - CLOB
Next I created simple mappings with 1:1 transform.
My physical page show:
I don't change anything in mappings proces, only set: TRUNCATE TARGET TABLE on True.
When I run my proces i get error:
oracle.odi.runtime.agent.exception.ExecutionEngineException: java.sql.SQLException: Invalid column type: 1111
at oracle.odi.runtime.agent.execution.sql.SQLCommand.bindResultSetColumns(SQLCommand.java:280)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.processRecord(SQLCommand.java:171)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.processRecord(SQLCommand.java:37)
at oracle.odi.runtime.agent.execution.Executor.performAction(Executor.java:444)
at oracle.odi.runtime.agent.execution.Executor.handleDataMovementTask(Executor.java:334)
at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:51)
at oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:206)
at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:117)
at oracle.odi.runtime.agent.execution.AbstractSessionTask.execute(AbstractSessionTask.java:886)
at oracle.odi.runtime.agent.execution.SessionExecutor$SerialTrain.runTasks(SessionExecutor.java:2225)
at oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:610)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:718)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:611)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doProcessStartAgentTask(TaskExecutorAgentRequestProcessor.java:800)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$1400(StartSessRequestProcessor.java:74)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:702)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:180)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:108)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Invalid column type: 1111
at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:5922)
at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:6551)
at oracle.jdbc.driver.OraclePreparedStatement.setNullInternal(OraclePreparedStatement.java:6315)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:12586)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:13397)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:269)
at oracle.odi.query.typemapping.PassThroughWriter.setValue(PassThroughWriter.java:64)
at oracle.odi.query.typemapping.OracleToJDBCWriter.setValue(OracleToJDBCWriter.java:87)
at oracle.odi.query.JDBCTemplate.setValue(JDBCTemplate.java:220)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.bindResultSetColumns(SQLCommand.java:277)
... 19 more
Ok, maybe I set wrong data-type for one of columns and I create simple select like:
select id, desc from public.layer
Next I paste this sql query to extract options, save and run. I get error:
ODI-1228: Task Insert new rows-LKM SQL to Oracle (Built-In)-Load LAYER1_AP fails on the target connection PSTG.
Caused By: java.sql.BatchUpdateException: ORA-12899: value too large for column "PSTG"."C$_0LAYER1"."DESC" (actual: 4754, maximum: 4000)
What can I do with this error?
In postresql I have json more than 4000, In oracle i created table with desc like clob but when I run mapping temp table like C$_0LAYER1 create column for varchar 4000.