Re: SQL*Loader: How to skip data

I guess you could use

    INTO TABLE a
    fields terminated by ','
    ( d DATE "to_date( substr( d, 1, 20 ), 'YYYY/mon/DD HH24:MI:SS' )"     , v CHAR
)

Some explaination: when you actually insert something else into column d, then 'd' can still be used in other parts of the SQL*Loader scripts to reflect the value that is actually found in the file, not the value that you want to insert in column d. For example:

    INTO TABLE a
    fields terminated by ','
    ( col1 CHAR "decode( col1, 'A', 'XYZ', col1 )"     , col2 CHAR "decode( col1, 'A', col1, col2 )"
)

This will insert 'XYZ' into col1 if it is actually 'A' in the file. Still, the decode for col2 uses col1. In that decode, col1 will not be represented as 'XYZ', but still as 'A'. The value 'A' is inserted into col2 if in the file col1 = 'A', even though col1 gets the value 'XYZ'. Otherwise, the given value for col2 is used. I guess the same applies to dates. You could use 'd' to refer to the text actually found in the text file.

Or, instead of using the delimiter, you could use the actual position, skipping 21 through 30. By head:

    INTO TABLE a
    ( d DATE pos(001:020) 'YYYY/mon/DD HH24:MI:SS'     , v CHAR pos(031:031)
)

By the way, I guess you'd also need a "optionally enclosed by" part.

Arjan. Received on Wed Aug 19 1998 - 19:39:10 CEST