Create JSON file using ODI

Christyxo

I am trying to create a JSON file populated by ODI mappings and created with the CREATE FILE procedure, but I can't generate the file at the end of the process as I keep getting the following error:

ODI-1228: Task Procedure-PRC_JSON_CONTACT-Create File fails on the target connection CFILE_CONTACT_local.
Caused By: java.sql.SQLException: ODI-40768: Could not save the file C:\Oracle\DEV\response\contact.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. Start of root element expected.
The data does not conform to the NXSD schema. Please correct the problem.

The topology is ComplexFile with a DTD file created using the Native Schema Builder with the translation type = json, schema name = XSDSCHEMA, standalone = true.

I reversed engineered the model successful, with 3 datastores being generated; ROOTELEMENT, CONTACT, and OPTIN, and I can use this model as a source to load the data into tables in the DB successfully, but not as a target.

I have 3 separate ODI mappings which load the results into these 3 tables, and the execution of these is successful. Each mapping uses Control Append, LKM SQL Multi-Connect, and IKM SQL to SQL Control Append. The operation logs shows the expected number of inserts in these mappings. I do not want to populate every field in the mapping but as with other issues on the complex file, NULLS can cause this process to fail so I've also tried by adding a valid output to every field in the mapping and still get the error. I don't really understand the logic of the ORDER columns but I've added sequential numbers that align with the order of the elements in the XSD.

The 3 mappings are placed inside 1 package, with a starting procedure running SYNCRONIZE FROM FILE, followed by the 3 mappings, followed by the procedure CREATE FILE "C:\Oracle\DEV\response\contact.json" FROM SCHEMA "XSDSCHEMA". It's the create file step is where the process fails with the above error.

The same error occurs even if I don't run the mappings in the package.

The same error occurs if I use the JSON model as both source and target in a mapping before creating the file.

I think the problem is specifically

Failed to translate XML to JSON. Start of root element expected.

but I don't know how to debug this of start to understand where this is going wrong. Does anyone have any thoughts?

User_SGGNZ

Can you try to generate the json file as part of the last mapping instead of creating a procedure. Also it works without 'synchronize from file' when file is target.

Christyxo

For the sake of posterity, I never managed to solve the above.

I used an alternative solution which used the database (12c) to translate the JSON rather than ODI.

My solution was to create a temporary table with a blob field having a json constraint, and then loaded the entire contents of my json file into this table using ODI.

CREATE TABLE 
  TMP_RESPONSE
(
  RESPONSE_VALUE   CLOB CONSTRAINT CK_JSON CHECK ( RESPONSE_VALUE IS JSON ) RELY DISABLE
) ;

When it was in the table, I used a procedure to handle the select and insert. The statement used the JSON_TABLE expressions to break it down within the database. Different example to the above but that was a year ago!

SELECT 
  j.*
FROM
  TMP_RESPONSE t
, JSON_TABLE
(
  t.RESPONSE_VALUE , '$[*]'
  COLUMNS
  (
    CUSTOMER_ID      VARCHAR2 ( 255 ) PATH '$.customer'
  , FIRST_NAME       VARCHAR2 ( 255 ) PATH '$.firstName'
  , LAST_NAME        VARCHAR2 ( 255 ) PATH '$.lastName'
  , NESTED PATH '$.products[*]'
    COLUMNS
    (
      PRODUCT_ID     VARCHAR2 ( 255 ) PATH '$.product'
    , PRODUCT_CODE   VARCHAR2 ( 255 ) PATH '$.shortName'
    , PRODUCT_NAME   VARCHAR2 ( 255 ) PATH '$.name'
    )
  ) j
) ;

I haven't tried to resolve the original issue since I posted my issue.

Christy