Re: sqlldr options

You can use the WHEN clause combined with the partitioning option. In other words, you can split up the partitions, treating each as a different table:

LOAD INTO TABLE tbl PARTITION (a)
WHEN (condition a)...

LOAD INTO TABLE tbl PARTION (b)
WHEN (condition b)
...

and so on.

If conditions are ranges (i.e. a date range), things may become complex, because the WHEN clause only accepts simple AND conditions, not ORs. If the partitioning criteria is by year's quarters for example, you will have to repeat the whole field specification section as many times as necessary:
INTO TABLE tbl

PARTITION (q1)

WHEN (month) = 'JAN'

(<field specs>)

INTO TABLE tbl

PARTITION (q1)

WHEN (month) = 'FEB'

(<field specs>)

INTO TABLE tbl

PARTITION (q1)

WHEN (month) = 'MAR'

(<field specs>)

INTO TABLE tbl

PARTITION (q2)                            -- Note the change of quarter

WHEN (month) = 'APR'

(<field specs>)

and so on.
Field "month" must be present in the field specifications, otherwise you can also use fixed field positions if that is your case, i.e. (38:40) = 'FEB'. This method works also with the DIRECT option. I hope your partitioning rule is not a complex range :-)

Anyway, you can also limit the number of rows using SKIP and ROWS in the OPTIONS section, consult the Utilities manual for more information.

Bye,
Flavio Received on Sat Apr 26 2003 - 17:35:27 CEST