Nested table and SQL*Loader

From: <giguette_at_postoffice.pacbell.net>
Date: Wed, 14 Mar 2001 08:07:43 -0800
Message-ID: <3AAF974F.63744E4A@postoffice.pacbell.net>

Oracle 8i

I'm having trouble loading data into a nested table with SQL*Loader. I'm trying to follow the examples in the Oracle documentation and several books I have, but they don't seem to work. I keep getting the error message:

SQL*Loader-403: Referenced column not present in table APPLICANT.

I'm not sure whether my tables are wrong, my control file is wrong, or both. I CAN successfully load data into the tables using INSERT statements.

Below is a simplified example of what I am trying to do.

Can anyone out there help?

FILE EXAMPLE.SQL ************************

/*----- Definition of abstract datatype for nested table -----*/

DROP TABLE Applicant CASCADE CONSTRAINTS; DROP TYPE REFERENCES_NT; CREATE OR REPLACE TYPE REFERENCE_TY AS OBJECT (

   Ref_Num           NUMBER(1,0),
   Ref_First_Name    VARCHAR2(10),
   Ref_Last_Name     VARCHAR2(10)

)
/

CREATE TYPE REFERENCES_NT AS TABLE OF REFERENCE_TY
/

/*----- Definition of "parent" table -----*/

CREATE TABLE Applicant (

   Id             NUMBER(4,0),
   First_Name     VARCHAR2(10),
   Last_Name      VARCHAR2(10),
   References     REFERENCES_NT,

constraint APPLICANT_PK PRIMARY KEY (Id) )
tablespace USERS
storage (INITIAL 128K next 16K minextents 1 pctincrease 0) NESTED TABLE References store AS REFERENCES_NT_STORE ;

/*----- Definition of sequence for Id number -----*/

DROP sequence AppID_seq;
CREATE sequence AppID_seq INCREMENT BY 1 START WITH 1 NOCACHE; COMMIT;
/*----- Mass-load values -----*/

host sqlldr userid=scott/tiger control='example.ctl'

SQL*LOADER CONTROL FILE EXAMPLE.CTL **************

LOAD DATA
infile 'example.dat'
INTO TABLE Applicant
(

   Id             "AppID_seq.nextval",
   First_Name     position(01:04) CHAR,
   Last_Name      position(06:15) CHAR,
   References     NESTED TABLE COUNT(1)
   (
      References     COLUMN OBJECT
      (
         Ref_Num        POSITION(28:28) INTEGER EXTERNAL(1),
         Ref_First_Name position(30:39) CHAR,
         Ref_Last_Name  position(41:50) CHAR
      )
   )
)

DATA FILE EXAMPLE.DAT **************

0001 Robert Smith 1 Jane Eddings 2 Frank Martel

0002 John       Doe        1 Guy        Madison    2 Elliot    
Ness      
0003 Richard    Lionheart  1 Robin      Hood       2
Saladin              
0004 Francis    Drake      1 Elizabeth  Tudor      2 Guy

Fawkes

Thanks.
John Giguette Received on Wed Mar 14 2001 - 10:07:43 CST