I am having difficulty testing a solution that involves exporting information from MS Access into a comma delimited file and then using SQL Loader to upload the data into an Oracle 9.02 database. The one MS Access export has approx. 100 fields that are being loaded to 6 Oracle tables. I am wanting to take five of the fields in the comma delimited file and wrap them into a varray field in the Oracle table. I do not have a problem loading the varray. My dilemma is commenting out the varray in the other sqlldr 'into table' statements.

I show you some of the code I am using. In the end, I'm after some workable example code that can show me how to comment out a varray using a variable to tell sqlldr how many fields to comment out.

In my Oracle ddl I create the type and table as follows:

CREATE or REPLACE TYPE skill_list AS VARRAY(5) OF NUMBER(4);

create table ddx.SKILL_OBJECT (
task_num NUMBER(10),
rank VARCHAR2(15),
rate VARCHAR2(20),
knowledge VARCHAR2(100),
skills skill_list);

In my sqlldr script I load the column with this field declaration:

skills VARRAY COUNT (num_skills)
(skills skill_list)),

where num_skills identifies the number of skill fields to expect.


I have tried the code that follows to no avail. Note: I am avoiding the use of positional field declarations due to the size of some of the columns and our choice for comma-delimiting the data.

skills VARRAY COUNT (num_skills)
(skills filler)),


Anyone have a solution or location to accurate sqlldr documentation to address this?


Thanks in advance.