Load a structured text data file using only one control file.

Question

I have a text file need to be loaded have structure like this (badly, I don't have permit to change):

MM/DD/YYYY 24HH:MI:SS NO_OF_REC
EMP_ID,EMPNAME,SALARY
.....

Ex:

12/24/2010 20:30:10 number_of_datarow_below
E0001,SMITH,5000
E0002,JOHN,7000
E0003,KEWELL,9000

Into one Table:

EMP(ISHEADER, HEAD_DATA_TIME, NO_OF_REC, EMP_ID,EMPNAME,SALARY)

Columns data type can be flexible.

Expected load result:

  ISHEADER  HEAD_DATA_TIME       NO_OF_REC     EMP_ID  EMPNAME  SALARY
  1         12/24/2010 20:30:10  3               
  2                                            E0001   SMITH    5000            
  2                                            E0002   JOHN     7000            
  2                                            E0003   KEWELL   9000            

My solution: I am using two control files:
   1. The first to load header (using option LOAD=1 and TRUNCATE mode).
   2. The second to load the rest of data (using OPTION SKIP and APPEND mode).

Is there any resolving way that use only one control file?

Thank you.

Answer 1

My SQL Loader is very rusty, but could you not use the WHEN clause somehow?

INTO TABLE emp
   WHEN rectype = 'E' 
   (rectype  POSITION(1:1)  CHAR,
   ...)
INTO TABLE emp 
   WHEN rectype != 'E' 
   (rectype  POSITION(1:1)  CHAR,
   ...)