ODI Studio 12c
I am a beginner in ODI.
I am trying to load data from a file to an Oracle database (12c). Problem is that one of the fields in the flat file is more than 4000 characters and the mapping fails with:
ORA-01461: can bind a LONG value only for insert into a LONG column
I see that the intermediate table C$_0DEFAULT is getting created with the offending field as VARCHAR2(4000). My final target table REVIEWS is, of course, created with a CLOB column for the same field.
The offending field in the flat file is "comments"
Flat file REVIEWS.csv
| 1 | listing_id | Numeric | 50 | 12 | null | | | | |
| 2 | id | Numeric | 50 | 12 | null | | | | |
| 3 | review_date | String | 100 | 100 | null | | | | |
| 4 | reviewer_id | Numeric | 50 | 12 | null | | | | |
| 5 | reviewer_name | String | 4000 | 4000 | null | | | | |
| 6 | comments | String | 4000 | 4000 | null | | | | |
Oracle REVIEWS table (final target)
| 1 | LISTING_ID | NUMBER | null | null | false | |
| 2 | ID | NUMBER | null | null | false | |
| 3 | REVIEW_DATE | VARCHAR2 | 100 | null | false | |
| 4 | REVIEWER_ID | NUMBER | null | null | false | |
| 5 | REVIEWER_NAME | VARCHAR2 | 4000 | null | false | |
| 6 | COMMENTS | CLOB | null | null | false | |
C$_0DEFAULT table after failed Run of the mapping
SQL> desc c$_0default
Name Null? Type
----------------------------------------- -------- ----------------------------
LISTING_ID NUMBER(12)
ID NUMBER(12)
REVIEW_DATE VARCHAR2(100)
REVIEWER_ID NUMBER(12)
REVIEWER_NAME VARCHAR2(4000)
COMMENTS VARCHAR2(4000)
My question is how can I ensure that the C$_0DEFAULT.COMMENTS column gets created with a CLOB column? Or, how can I load data directly in the REVIEWS table?
Thanks for any suggestions.