Wednesday, November 6, 2024

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

 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. 

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