Partitioned tables & sql loader question

[rogers42]

Hi Folks,

I need help with loading a partitioned table using the sql loader. Details are as follows

Table Structure:
Name Null? Type
------- -------- ----------------------------
FIELD_1 VARCHAR2(10)
FIELD_2 VARCHAR2(10)

Note:

The table has seven partitions (for each day of the week).

Sample Data:
1, 02-06-2009
2, 02-07-2009

My sql loader control file:

LOAD DATA
TRUNCATE INTO TABLE TEST
PARTITION field_2)
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
field_1 CHAR,
field_2 CHAR to_char(to_datefield_2, 'MM-DD-YYYY'), 'FMDAY')
)

I have also tried the date conversion logic in the partition clause but no luck.

Can somebody please let me know if I am doing something wrong or this can't be done via the sql loader ?

Any work arounds will be appreciated.

Thanks

rogers42

 

[Dagon]

I don't think you can specify a partition with the truncate option and you certainly can't refer to a field in the file that way. You will probably need to do this slightly differently e.g. by first loading up the distinct field_2's into a table and then using that to generate a list of all the partitions to truncate.