Dear All,
Our database is oracle 10g r2 and OS is solaris
We would receive csv files to a particular directory on server each day.
File Format look like:
-------------------------
H00,SOURCE_NAME,FILE_CREATED_DATE
RECORD_TYPE,EMP_ID,EMP_NAME,EMP_DOB(DDMMYYYY),EMP_HIRE_DATE(DDMMYYYY),EMP_LOCATION
T00,RECORD_COUNT
EMPLOYEE TABLE STRUCTURE
---------------------------
EMP_ID NOT NULL NUMBER ,
EMP_NAME NOT NULL VARCHAR2(10) ,
EMP_DOB DATE,
EMP_HIRE_DATE NOT NULL DATE,
EMP_LOCATION VARCHAR2(80)
Sample File:
-------------
H00,ABC,21092013
"R01",1,"EMP1","14021986","06072010","LOC1"
"R01",20000000000,"EMP2","14021-987","06072011",""
,***,"EMPPPPPPPPPPP3","14021988","060**012","LOC2"
"R01",4,4,"14021989","06072013",
T00,4
we need to validate each record excluding header and trailer for:
DATATYPE, LENGTH,OPTIONALITY, and other date validations such as EMP_HIRE_DATE can not be less than EMP_DOB
In case of any data errors we need to send a response file for corresponding source file.
we have predefined error codes to be sent in the response file.
ERR001 EMP_ID can not be null
ERR002 EMP_ID exceeds 10 digits
ERR003 EMP_ID is not a number
ERR004 EMP_NAME has to be text
ERR005 EMP_NAME length can not exceed 10
ERR006 EMP_NAME can not be null
ERR007 EMP_DOB is not a date
ERR008 EMP_DOB is not in ddmmyyyy format
ERR009 EMP_HIRE_DATE is not a date
ERR010 EMP_HIRE_DATE is not in ddmmyyyy format
ERR011 EMP_HIRE_DATE can not be null
ERR012 EMP_LOCATION has to be text
ERR013 EMP_LOCATION length can not exceed 80
ERR014 EMP_HIRE_DATE can not be less than EMP_DOB
ERR015 Field missing in the record
ERR016 More number of fields than allowed
1.Do I need to create external table before processing each file.(EMP1.txt,EMP2.txt)?
2.How to generate these error codes in case of respective failure scenarios and to log into an exception table?
3.response file needs to have entire record and a concatination of all the error codes in the next line.
4.what would be a better approach among
creating an external table with all char(2000) fields and writing a select statement
such as select * from ext_table where (emp id is not null and length(emp_id)<=10 and....to select only proper data);
or creating the external table to be same as employee table and creating a bad file? if this is the preferred how can I generate the custom error codes?
Could you please help me in achieving this!
Warm Regards,
Shankar.