Re: SQL*Loader only loads first row into nested table

Thomas Kellerer schrieb:
> 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

The following control file should work with your data.

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 '|'      (group_name CHAR terminated by ';')    ,
   flag char(1) TERMINATED BY WHITESPACE )

Best regards

Maxim Received on Tue Sep 06 2005 - 09:03:24 CDT