Database Software

Dear all,

i am using oracle 10g on windows server.

i have a logfile on a remote server. every line in that log file is like below

LogIN Tue 09/02/2014 10:10:48 es18 N419FS40 1

i create a table for that like below;

Create table free_pc (log_in varchar2(10),log_day varchar2(10),log_date date,log_time date,log_user varchar2(30),log_Lab varchar2(30),log_pc varchar2(30), log_status char(1));

i have two problems;

1. N419FS40 , the N419 is the name of a student Lab, while FS40 is the computer name in that lab. i want the first four charachters to be inserted in Log_lab column and the rest of charachters iin Log_pc column. my control file is below

 LOAD DATA
   INFILE "\\remote_location\login02_09_14.txt"
   APPEND
   INTO TABLE free_pc
   FIELDS TERMINATED BY WHITESPACE
   (log_in,
    log_day,
    log_date DATE 'DD/MM/YYYY',
    log_time DATE 'HH24-MI-SS',
    log_user,
    log_Lab,
    log_PC,
    log_status
   )

how to do that?

2. the log file is generated on the server every day with different name, it cancatenate the current date with LOGIN word, e.g. login02_09_14.txt, login03_09_14.txt and login04_09_14 etc. in my INFILE tag, how could i put the name so that it automatically take the name from the remote location?

Thank you.

Regards.