2011-04-14

how to use a sequence in sql loader ( sqlldr )

"The SEQUENCE function generates a unique value in the column LOADSEQ. This function finds the current maximum value in column LOADSEQ and adds the increment (1) to it to obtain the value for LOADSEQ for each row inserted."


http://www.pafumi.net/sql_loader.htm#3

example:
  LOAD DATA
   INFILE *
   APPEND
   INTO TABLE emp
   FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
   (empno, ename, job, mgr,
   hiredate DATE(20) "DD-Month-YYYY",
   sal, comm, deptno CHAR TERMINATED BY ':',
   projno,
   loadseq  SEQUENCE(MAX,1))

There is also a second workaround: http://www.orafaq.com/wiki/SQL*Loader_FAQ 
One can also populate columns with static or derived values. However, this only applies for the conventional load path (and not for direct path loads)

LOAD DATA
  INFILE *
  INTO TABLE modified_data
  (  rec_no                      "my_db_sequence.nextval",
     region                      CONSTANT '31',
     time_loaded                 "to_char(SYSDATE, 'HH24:MI')",
     data1        POSITION(1:5)  ":data1/100",
     data2        POSITION(6:15) "upper(:data2)",
     data3        POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)

Niciun comentariu:

Trimiteți un comentariu