Use user-defined columns in load data statement : CSV Load « Table « Oracle PL / SQL

1. Oracle PL / SQL
2. Table
3. CSV Load

Use user-defined columns in load data statement

 

create table dept
( deptno  number(2) constraint emp_pk primary key,
  dname   varchar2(14),
  loc     varchar2(13)
)
/

create or replace 
function my_to_date( p_string in varchar2 ) return date
as
    type fmtArray is table of varchar2(25);

    l_fmts  fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy', 
                                  'dd/mm/yyyy',
                                  'dd/mm/yyyy hh24:mi:ss' );
    l_return date;
begin
    for i in 1 .. l_fmts.count 
    loop
        begin
            l_return := to_date( p_string, l_fmts(i) );
        exception    
            when others then null;
        end;
        EXIT when l_return is not null;
    end loop;

    if ( l_return is null ) 
    then
        l_return := 
           new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *  
                     p_string, 'GMT', 'EST' );
    end if;

    return l_return;
end;
/

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  LAST_UPDATED "my_to_date( :last_updated )"
)
BEGINDATA
10,Sales,BC,01-april-2001
20,Accounting,BC,13/04/2001
30,Consulting,BC,14/04/2001 12:02:02
40,Finance,BC,987268297
50,Finance,BC,02-apr-2001
60,Finance,BC,Not a date

drop table dept;

 

Related examples in the same category