Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Export json column from postgresql to oracle CLOB

choracy69Mar 26 2019 — edited Mar 26 2019

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.

choracy69

Ok, I create new datatype for postresql like: VARCHAR_JSON and set Converted to: ORACLE = CLOB. And it's working where my column in postregsql has JSON DATATYPE i set for it my new VARCHAR_JSON and it is converting to clob ok.

But with this solution I have some error - when column with json datatype is null I get error like: