Wednesday, November 6, 2024

How to import large text file (unstructured raw data) content into Oracle Table 's column using ODI 12c ( Oracle Data Integrator 12c )

In one of our client project, we came across a requirement to import large size text file content (unstructured raw data, more than ~10000 Characters ) into Oracle table's column. The files were API call responses in JSON format and had to be stored in Oracle tables using ODI (Oracle Data Integrator 12c) ELT tool.

Source Text File: The file has JSON data



Target Table: Stored the above JSON data as is in a Oracle Table's Column


Implemented the ODI mapping by following usual steps and it was all fine. But started facing issue the below issue when the content of the source file was more than 4000 characters.

 


Initial Implementation of Mapping in ODI - 

1.Defined the target table column type as CLOB 

2. Configured the File technology Physical schema and Logical schema in ODI topology  for File and     Oracle technology Physical schema and Logical schema for the target oracle database. then we created the datastore for File and Oracle table     in ODI model.  In File datastore of ODI model, we defined the File Format to Fixed and File column data type as Sting with 4000  and   defined the CLOB data type for target table column.  

3. Developed ODI mapping by considering the File datastore as source and oracle table as target. 

When we run the mapping we received the below error. 

 java.sql.SQLException: ORA-12899: value too large for column "DB NAME"."TABLE NAME"."Column Name" (actual: 5000, maximum: 4000)

We did some research over the internet and came to know that though we defined target table's column as CLOB, ODI would  not convert the source string type column  of File datastore to CLOB automatically. To resolve this we had to perform the below steps. 

Solution - 

1. Go to Topology  -> Technologies -> Expand the  "File" technology

2. Then expand "Data Types" option, right click and open "Fixed String". Here we proffered "Fixed String" data type because we  did not want to change the behavior of "Sting" data type. 

3. Click on "Convert To" option in the "Fixed String" properties window and change to data type to CLOB for Oracle technology and save it. 


4. Change the data type of  Source File Datastore column to "Fixed String" and define the maximum length. 


5. Run the mapping. 

With this configuration changes we were able to successfully load the file content CLOB type column of Oracle table.