SQL*Loader only loads first row into nested table

From: Thomas Kellerer <WVIJEVPANEHT_at_spammotel.com>
Date: Tue, 06 Sep 2005 14:05:41 +0200
Message-ID: <3o5f0lF4aoosU1@individual.net>

Hello all,

I'm trying to load a flat file into a table with a nested table using Oracle 9.2

The table definition for the destination table is:

CREATE OR REPLACE TYPE grouplist AS TABLE OF VARCHAR2(100);

CREATE TABLE user_profile
(

    userid      VARCHAR2(60) PRIMARY KEY,
    lastname    VARCHAR2(100),
    firstname   VARCHAR2(100),
    usergroups  grouplist,
    flag        CHAR(1)

)
NESTED TABLE usergroups STORE AS user_group_assignment ;

The control file for SQL*Loader looks like this:

options (skip=1)
LOAD DATA CHARACTERSET UTF8
TRUNCATE
INTO TABLE user_profile
FIELDS TERMINATED BY '|'
     TRAILING NULLCOLS
(

   userid,
   lastname,
   firstname,
   usergroups NESTED TABLE TERMINATED BY ';'

              OPTIONALLY ENCLOSED BY '"' (group_name),    flag
)

The input file looks like this:

USERID|LASTNAME|FIRSTNAME|GROUPS|FLAG
1|Dent|Arthur|Group1;Group2;Group3|N

Now when I run SQL*Loader I get an error message in the log file stating

Record 1: Rejected - Error on table USER_PROFILE, column FLAG. ORA-01401: inserted value too large for column

By increasing the size for the flag column I could verify that the loader is inserting "Group1" into the nested table, and then trying to insert "Group2;Group3" into the FLAG column.

How do I tell the loader that Group1;Group2;Group3 should go into the nested table as rows?

Ideally I would not like to use a nested table, and have SQL*Loader put the usergroups rows into a "regular" table, but it seems that this is not possible.

I am aware that I can put everything into a staging table, and the process the usergroups column manually, but for the fun of it I'd like to do it in one go.

Any ideas?

Best regards
Thomas Received on Tue Sep 06 2005 - 07:05:41 CDT