Re: SQL*Loader: How to skip data

You can use POSITION to specify the
start and end position of the field

Look at this example

LOAD DATA
INFILE 'ulcase7.dat'
APPEND
INTO TABLE emp
  WHEN (57)='.'
  TRAILING NULLCOLS
  (hiredate SYSDATE,
   deptno POSITION(1:2) INTEGER EXTERNAL(3)

            NULLIF deptno=BLANKS,
   job      POSITION(7:14)   CHAR  TERMINATED BY WHITESPACE
            NULLIF job=BLANKS  "UPPER(:job)",
   mgr      POSITION(28:31)  INTEGER EXTERNAL TERMINATED BY WHITESPACE
            NULLIF mgr=BLANKS,
   ename    POSITION (34:41) CHAR  TERMINATED BY WHITESPACE
            "UPPER(:ename)",
   empno    INTEGER EXTERNAL  TERMINATED BY WHITESPACE,
   sal      POSITION(51)  CHAR  TERMINATED BY WHITESPACE
            "TO_NUMBER(:sal,'$99,999.99')",
   comm     INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
            ":comm * 100"

HTH Madhu Cherukuri

Kirill Richine wrote in message <6rer2b$704$1_at_scapa.cs.ualberta.ca>...
>Hi!
>
>Suppose one has table A with two columns:
>DATE and VARCHAR2
>
>Suppose one needs to load this table from the following datafile
>
>1998/aug/19 15:25:26.320000, 'a'
>1998/aug/19 16:25:27.163200, 'b'
>....
>
>Is there a way to do this?
>
>I am trying to
>
>INTO TABLE a
>fields terminated by ',' (
> d DATE 'YYYY/mon/DD HH24:MI:SS'
> v CHAR
>)
>
>But it generates errors because of the remainder of the date (the
>microseconds).
>
>This situation is not farfetched and occurs when one wants to move
>data from SQLANY50 to Oracle.
>
>Thanks.
>k&
Received on Thu Aug 20 1998 - 03:09:46 CEST