I have come across a requirement to import text files content (unstructured raw data) into Oracle table's column. These files are API call responses and they need these response files to be stored on tables. We had to automate the file reading and storing the content on oracle table using ODI ELT tool.
Oracle has CLOB data type which can store huge text data. But we faced an issue when we configured the ODI mapping to read the content from file to store it on table.
How we implemented in ODI -
1.We defined the target table column type to CLOB
2. We configured the File technology Physical schema and Logical schema in ODI topology for File and Oracle technology 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 data type as Sting with 4000 as length for the column and defined the CLOB data type for target table column.
3. We 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 of File datastore to CLOB automatically. To resolve this we need 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. Run the mapping.