Json File Processing in ODI 12C

Json file processing in ODI 12C:

Oracle Data Integrator Version 12.2.1 (Build ODI_12.
Oracle XE 11g
OS: Windows 8

This article will walk through the following steps to load data from Json file to Oracle table :

1. Json file walk through
2. Setup Data Server for JSON
3. Wizard to generate XSD file using Native format builder
4. Create mapping and execute the job

This article will not cover the installation and setup of Oracle Data Integrator. The assumption is ODI 12c already installed and is working correctly. For details on how to install and configure ODI Studio see this post.

Before creating the map, lets take a look at the Json file. I have created this file using facebook graph API. It will have the list of public figures and their post information. Each post will have like and comment counts. The URL I am using for this josn file is

Json file from facebook graph API


And the first element gets repeated for different public figures. So in total I have 4 public figures in my json file. There are some additional fields as well but we will not use them at the moment. If you want more data, change the limit parameter in the Graph API request. .

Json file from facebook graph API

Target table structure for our mapping:


Now connect to ODI studio and create a Data Server under Complex File technology. Note that the complex file driver works in a series of steps.

  1. The complex file is translated to an intermediate XML file using the Native Schema (nXSD) file. Note that no physical file is created for the intermediate XML file but a streaming XML structure.
  2. The driver loads the XML structure and data into a relational schema, using a XML to SQL Mapping.
  3. The user works on the relational schema, manipulating data through regular SQL statements or specific driver commands for driver operations.
  4. Upon disconnection or user request, the Complex Files driver synchronizes the data and structure stored in the schema back to the complex file.

To know more on this refer oracle documentation.

Here is the driver details that I have configured.

Since all the complex file needs to have one XSD , lets generate it using the in-build wizard. Click on Edit nXSD button and follow the steps below.

Native format builder in ODI 12c

Select the location where xsd file will be created.

Native format builder in ODI 12c

Select Json Interchange Format

Native format builder in ODI 12c

Select the json file.

Native format builder in ODI 12c

This is the preview of XSD file. Now close the wizard.

Native format builder in ODI 12c

Next we have to create a model. Follow the standard process for Complex File technology and do the selective reverse engineering. Choose the required tables for our mapping.  In Similar fashion you can create the model for oracle table.

Now create a mapping and drag all the tables as shown above. The joins will be automatically mapped based on primary and foreign key relationship.

Logical diagram of json data stores

We will now do the mapping with target table. I will go ahead with the mapping for name, fan_count, category, likes total_count, comments total_count. Lets take a look at the diagram generated in physical tab.

Physcial diagram of json data stores

We are now done with the development. Save the mapping and execute it.

As you can see I am getting following error in operator.

ODI-1227: Task Insert new rows-LKM SQL to SQL (Built-In)-Load DEFAULT_AP fails on the source connection ds_json.
Caused By: java.sql.SQLSyntaxErrorException: unexpected token: INNER : line: 8
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)

I did some quick research and didn’t find any satisfactory information from Google. Then I looked at the properties of Complex File Technology and thought to give a try with the high lighted check box below which was unticked earlier. Save everything and execute the mapping again.

This time the execution is successful. Now ODI generates parenthesis for all the tables that are joined with each other.

This is how the data look in the target table.

Thats it for today. Let me know if you need any help in setting up the mapping. In the coming post I will do the same in Talend Open Studio and try to compare the development effort required in both tools. Also one thing I noticed here is that if there are two complex type having same name then ODI is not drilling down in the hierarchy. Thats why I had to rename the likes element in my json file to some unique name. I was assuming ODI will give some auto naming convention to avoid multiple data stores having same name but thats not the case.

Thank you and stay tuned!!!

About Bhabani 86 Articles
Bhabani has 12 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.


  1. Hi ,
    How to generate this Json file and what are the KM’s that you have been used here?
    I tried to create Complex File data server in 12c in file I tried to browse the path but its not opening. So copied and pasted the file name including the file name and then I tried to create xsd by wizard but its getting failed. What are the steps that you followed could you please elaborate little.


  2. Hi Babani,

    This is kavya. We have one requirement. Requirement is to develop odi pkg which will change Max connections parameter in online version of OBIA RPD. Biserverxmlcli is used to modify an online repository without any need for service bounce. Can you help me how to do in odi .


  3. Hi,

    Can multiple JSONs sources be joined by a common element value (via ODI native Join function) ? I can map individual JSONs to target tables however when I try to join I am getting a “Caused by: org.hsqldb.HsqlException: object name already exists: XSDSCHEMA_ROOT_ELEMENT” error


  4. I have loaded json file to DB table as mentioned above on windows environment it went fine but while processing file from unix environment having issues after creating data server test connection issues Could you please post or help me

  5. Hi,

    we have a requirement like the source is CSV file and target is JSON file format. so could you please help on how to do in the odi 12.1.3 , even am unable configure the setup in ODI Topology.

    please provide link or any related documentation.


  6. Hi Babani,

    I am trying to create JSON from oracle table but not getting exact mapping. Can you please help me here.


  7. Hi Babani,
    I am getting below response when running mapping from JSON to Oracle table.What is the resolution for this and why am I getting this error.

    ODI-1227: Task Insert new rows-LKM SQL to Oracle (Built-In)-Load DEFAULT_AP fails on the source connection PRODUCTNAME_CATEGORY1.
    Caused By: java.sql.SQLSyntaxErrorException: unknown token: VALUES_3_2.
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    at com.sunopsis.jdbc.driver.xml.SnpsXmlStatementRedirector.executeQuery(SnpsXmlStatementRedirector.java:126)
    at com.sunopsis.jdbc.driver.xml.SnpsXmlStatement.executeQuery(SnpsXmlStatement.java:49)
    at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:196)
    at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:204)
    at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:100)
    at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:38)
    at oracle.odi.runtime.agent.execution.Executor.handleDataMovementTask(Executor.java:309)
    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.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: org.hsqldb.HsqlException: unknown token: VALUES_3_2.
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ParserBase.read(Unknown Source)
    at org.hsqldb.ParserBase.readThis(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSelect(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
    at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    … 24 more

Comments are closed.