in ODI12c JSON files are supported as both a source and a target using the Complex File type technology.
I've successfully executed a mapping that takes a JSON source file ( I'm using a JSON files which is provided
with Oracle 12c at $ORACLE_HOME/demo/schema/order_entry). This is a nested JSON file that produces multiple
logical tables: PO, LINEITEMS, PART, SHIPPINGINSTRUCTIONS, ADDRESS, PHONE.
I'm now trying to map that same JSON source file to a JSON target. I just want to duplicate the JSON file. I have
two physical data servers defined under Complex File Type technology: PO_JSON_INBOUND and PO_JSON_OUTBOUND,
both of which use the same native XSD file, but each has a different schema: POXSDIN and POXSDOUT respectfully.
In my mapping (POJson2Json), I simply map all the logical table in my JSON_INBOUND
model to my JSON_OUTBOUND model, This executed fine using the LKM SQL to SQL (built-in).GLOBAL KM.
To write the JSON file, I create a package that sends the result of my mapping (POJson2Json) to a Procedure step: Create JSON FILE
This procedure has a single task which does a CREATE FILE "file name" FROM SCHEMA "schema name"
This fails with the following error with the following stack trace:
ODI-1228: Task Procedure-Create JSON FILE-create_json fails on the target connection PO_JSON_OUTBOUND.
Caused By: java.sql.SQLException: ODI-40768: Could not save the file /tmp/outbound/po_out.json because a class java.sql.SQLException occurred and said: java.sql.SQLException: java.sql.SQLException: oracle.odi.jdbc.driver.xml.io.exception.PipelineStageException: ORABPEL-15236
Translation Failure.
Failed to translate XML to JSON. Expected 'EOF'.
The data does not conform to the NXSD schema. Please correct the problem.
at com.sunopsis.jdbc.driver.xml.SnpsXmlFile.writeToFile(SnpsXmlFile.java:836)
at com.sunopsis.jdbc.driver.xml.SnpsXmlConnection.internalExecute(SnpsXmlConnection.java:794)
at com.sunopsis.jdbc.driver.xml.SnpsXmlConnection.preprocessStatement(SnpsXmlConnection.java:1422)
at com.sunopsis.jdbc.driver.xml.SnpsXmlStatement.execute(SnpsXmlStatement.java:30)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:205)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:141)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:28)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:52)
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:2227)
at oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:607)
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:745)
Caused by: java.sql.SQLException: java.sql.SQLException: java.sql.SQLException: oracle.odi.jdbc.driver.xml.io.exception.PipelineStageException: ORABPEL-15236
The data does not conform to the NXSD schema. Please correct the problem.