Malformed INSERT with Table to File mapping

User_2XVAY

Hello All,

Could you help me with my DB Table to Flat File mapping in ODI studio.

This is just a simple mapping for testing/development/purposes. I've created the necessary models, both components are visible in the Editor, the mapping validation passes.

However, when executing, I receive the following error:

ODI-1217: Session ORACLE_TABLE_TO_FILE (332) fails with return code ODI-1298.
ODI-1226: Step Physical_STEP fails after 1 attempt(s).
ODI-1227: Task SERIAL-MAP_MAIN- fails on the source connection <Empty Value>.
ODI-1298: Serial task "SERIAL-MAP_MAIN- (50)" failed because child task "SERIAL-EU-MEMORY_ENGINE_UNIT (80)" is in error.
ODI-1298: Serial task "SERIAL-EU-MEMORY_ENGINE_UNIT (80)" failed because child task "Insert new rows-LKM SQL to File-Load S_AP (90)" is in error.
Caused By: oracle.odi.runtime.agent.exception.ExecutionEngineException: java.sql.SQLException: ODI-40480: Malformed INSERT statement.
   at oracle.odi.runtime.agent.execution.sql.SQLCommand.doInitializeStatement(SQLCommand.java:119)
   at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:148)
   at oracle.odi.runtime.agent.execution.sql.SQLCommand.getBatchCommand(SQLCommand.java:410)
   at oracle.odi.runtime.agent.execution.sql.SQLCommand.processRecord(SQLCommand.java:168)
   at oracle.odi.runtime.agent.execution.sql.SQLCommand.processRecord(SQLCommand.java:38)
   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:216)
   at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:128)
   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.StartScenRequestProcessor.access$2700(StartScenRequestProcessor.java:85)
   at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:917)
   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: ODI-40480: Malformed INSERT statement.
   at com.sunopsis.jdbc.driver.file.SQLManager.buildInsertValues(SQLManager.java:132)
   at com.sunopsis.jdbc.driver.file.SQLManager.buildRowFromInsert(SQLManager.java:392)
   at com.sunopsis.jdbc.driver.file.FilePreparedStatement.buildGenericRow(FilePreparedStatement.java:75)
   at com.sunopsis.jdbc.driver.file.FilePreparedStatement.<init>(FilePreparedStatement.java:56)
   at com.sunopsis.jdbc.driver.file.FileConnection.prepareStatement(FileConnection.java:416)
   at sun.reflect.GeneratedMethodAccessor68.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter$OnDisconnectCommandExecutionHandler.invoke(OnConnectOnDisconnectDataSourceAdapter.java:231)
   at com.sun.proxy.$Proxy50.prepareStatement(Unknown Source)
   at sun.reflect.GeneratedMethodAccessor68.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at com.sunopsis.sql.SnpsConnection$ConnectionProxyInvocationHandler.invoke(SnpsConnection.java:143)
   at com.sun.proxy.$Proxy46.prepareStatement(Unknown Source)
   at oracle.odi.runtime.agent.execution.sql.SQLCommand.doInitializeStatement(SQLCommand.java:105)
   ... 21 more

The alleged malformed INSERT (copied from the xml export) is as follows:

INSERT INTO "C:\Users\adrian.grzebyk\Documents\adgr.OutFile"
 VALUES
 ( 
   :T1 ,
   :T2 
 ) 
/*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=OutFileSNP$CRLOAD_FILE=<?=snpRef.getObjectName("L", "dest_file.txt", "D") ?>SNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=0x0009SNP$CRFILE_SEP_LINE=0x000D0x000ASNP$CRFILE_FIRST_ROW=0SNP$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=T1SNP$CRTYPE_NAME=STRINGSNP$CRORDER=1SNP$CRLENGTH=50SNP$CRPRECISION=50SNP$CRACTION_ON_ERROR=0SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=T2SNP$CRTYPE_NAME=STRINGSNP$CRORDER=2SNP$CRLENGTH=50SNP$CRPRECISION=50SNP$CRACTION_ON_ERROR=0SNP$CR$$SNPS_END_KEY*/

Can you tell me what I'm doing wrong?

Thanks,

Adrian

Christyxo

This can be caused by the knowledge modules that you are using. Assuming that you are using a control append approach:

1. On the logical panel of your mapping, select the target table, and open the properties. Select the "target" option, and choose "control append" as your integration option.
2. On the physical panel of your mapping, select the AP component and open the "loading knowledge module" option and choose "LKM SQL Multi-Connect" as your loading knowledge module.
3. Select the target component and open the "integration knowledge module" option and choose "IKM SQL to File Append"

Try your mapping again and see if you get the same error,