This content has been marked as final. Show 8 replies
Create a function which takes input srting and returns number
Inside the function check for date validation
Then use that function as a constraint in ODI .
You interface will load only valid date and invalid one will be populated in error table .
You can generate a file from the error tale for your reference.
from your reply,i understand that you are asking to put that function as constraint on target.but the problem with that is,the data will be validated for DATE datatype first and then only the constraint will be checked after insertion.here our interface is failing,so we are not able to insert the data even which later should be checked by the constraints.
Break the interface into 2 part.
1st one loads a temp table which shore the date data in a varchar2 type column .
NO validation required and data will be passed from your file to this tem table.
2nd one loads data from temp table into your target
In the 2nd interface , put a filter on your date column coming from source . some thing like
Here I am using a function
CREATE OR REPLACE FUNCTION IS_VALID_DATE_CHECK(p_strval in VARCHAR2)
l_dateval := TO_DATE(p_strval,'dd/mm/yyyy');
WHEN OTHERS THEN
This filter will ensure that only valid dates gets passed to Target.
After this you can do a MINUS query of Target and TEMP table to figure out the invalid dates and prepare a file for it.
thanks for the reply.but when we are trying to use temporary table in the 2nd interface,we are unable to enable "use temporay table as subselect" opition.so the interface is getting failed.
so please let us know where we are missing out something and can we create a temporary target from a file?
thanks for the reply.but when we are trying to use temporary table in the 2nd interface,we are unable to enable "use temporay table as subselect" opition.so the interface is getting failed.No need to select this option. We are interested to load data into a relational table only.
After that we are going to check for valid date and load those into final table.
so please let us know where we are missing out something and can we create a temporary target from a file?Yes , you can create a yellow interface for this requirement
we want to use a temporary table as target .we dont want to create any relational table in the database.is it possible to do ?
You may find another approach to do the same but no in this approach .
Please help me understand this.Are you saying that we cant create a temporary interface having file as source and temporary table(not RDBMS) as target?