i am just a newbie to ODI-Functionality.
what i am trying to do here is populating an RDBMS table with data from Delimited Flat file(CSV).
Data Load doesn't happen to the target table with error in Execution step.
ODI-1228: Task Load EMP_TRG_1-IKM Oracle Merge- fails on the target connection EMP_TRG.
Caused By: java.sql.SQLDataException: ORA-01843: not a valid month
I could check Data gets loaded to Staging Area but after that no Load happens.
in the Flat file-SOURCE date is in MM-DD-YY format and i changed my ODI server machine's SYSDATE to default MM-DD-YY and re ran the mapping but still no success.
could anyone please help me handling Date format type for SOURCE-TARGET mapping and data loading?
Note:If i remove the HIREDATE attribute i could load the data to Table
This is a common error for newbies.
You better use the to_date to resolve this.
For exmple to_date('17-12-80','dd-mm-yy')
If nothing happens create a temptable having all column datatype as varchar2(100). Now you can easily load data into this table and then load from this table to the target table using to_date function.
Thanks Bhabani for a quick turn around.
I could sense that the issue has something to do with the DATE type function. but the worry is how do i incorporate the solution?
in source flat file after i do a Reverse Engineering the Attributes i get if i change their data type and format especially for DATE datatype for HIREDATE Attribute , then the data load doesn't happen on to Staging area.
if i don't change and keep the DATA type as it is then Data load happens to Staging. in the Datastore that i create from the model has all ATTRIBUTES reverse engineered but if i change the format and data type of any ATTRIBUTE it doesn't take effect.
i know there has something that needs to be done with DATE data type.
my TARGET table create script is:
CREATE TABLE EMP_TRG
CONSTRAINT TAB_EMP_PK PRIMARY KEY(EMPNO)
SOURCE is a flatfile.
where do i incorporate the to_date function and the requirement being that the HIREDATE Attribute data should be of DATE type on TARGET
i followed the below OBE but have a different flat file to test where in i have a DATE ATTRIBUTE.
As i said use the this function in the target expression ( for ex: to_date(SOURCE_TAB_ALIAS.HIRE_DATE,'dd-mm-yy') ). ODI will generate the code by using this function and can easily load in to the target table. The objective is to convert from varchar to date.
You better copy the code generated for "insert into C$ table", "Insert into I$ table" and "load to to target table". This will give sufficient clue to understand why it is not working for you.
If you are still confused feel free to ping my email id.
Thanks for your inputs on my query.
Now i am able to load data to target DB, since i am complete new to the tool its getting difficult to play around it.i work on the INSTALL/CONFIG/Deployment part of ODI12c
i used to_date function for converting this string data to date type but what i observed on the target is something different.
SOURCE DATA:17-12-80(dd-mm-yy:17th December 1980)
Function used on TARGET Expression field -> to_date(SOURCE_ALIAS.HIREDATE,'dd-mm-yy')
Target data shows date in the format->2080-12-17 00:00:00.0
plz suggest on the same do we have something on to_date function to be controlled with??
Just to be more precise regarding the issue which is seen: when i try doing a "view data" from the Target model-Datastore the date is shown as 2080-12-17 00:00:00.0
But the Date format is proper when queried from the schema through SQL Developer. the date data is shown up as 17-DEC-80(DD-MON-YY) format though we had fed 'dd-mm-yy' format to the TARGET expression.
Viewing data will always differ from one client to other client as per their own setup. You may see the date in a different way in TOAD where as it may vary in SQL Developer or in SQL Plus. So it shouldn't be a problem unless you load this data in to a file. you said the source data is 17-12-80 and the target data is 2080-12-17 00:00:00.0. Here I dont see any issue with this as both of them points to same date (only the format is different). Does it make sense ?
If you face any issues while loading date type of data, you can just verify the NLS_DATE_FORMAT.
That's very true and makes sense.
I was just wondering if i could get to see the DATE from the ODI tool in 17-12-80 format , the reason being we assume 80 as 1980 but ODI TARGET MODEl-> VIEW DATA shows it as 2080-12-17 00:00:00.0., which is no calender year in real.as i mentioned earlier my SYSDATE format is MM-DD-YY.
In my SOURCE Model Data store i tried hard coding the date format as mm-dd-yy and reran the mapping but still in vain.
now since from you i have come to know how i should resolve the data load issue i was facing earlier now this Data viewing is just such a trivial thing but i would need your help to learn how to avoid this.
it is desirable to see the year as YY i.e 80 and incsae year is to be dispayed in YYYY format then it should be 1980 instead of 2080 which is unreal as of now. many thanks for bearing with me till now.