use case statement to check the value in load data statement
create table dept
( deptno number(2) constraint emp_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "case when length(:last_updated) <= 10
then to_date(:last_updated,'dd/mm/yyyy')
else to_date(:last_updated,'dd/mm/yyyy hh24:mi:ss')
end"
)
BEGINDATA
10,Sales,BC,1/5/2000 12:03:03
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000 01:23:00
40,Finance,BC,15/3/2001
drop table dept;
Related examples in the same category