Mega Code Archive

 

Categories / Oracle PLSQL / Table

 

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;