Nested table and SQL*Loader

From: John Giguette <nobody_at_pacbell.net>
Date: Thu, 29 Mar 2001 19:04:34 -0800
Message-ID: <oJSw6.1006$W.160976_at_news.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 Fri Mar 30 2001 - 05:04:34 CEST