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.