nested table and SQL loader

From: <johnsonmj_at_my-deja.com>
Date: Thu, 21 Oct 1999 03:37:44 GMT
Message-ID: <7um1q7$8oq$1@nnrp1.deja.com>

Hi,

I am trying to use the SQL*Loader to load data into a table that has a nested table column. When I run it, I get an error: SQL*Loader-418: Bad datafile datatype for column CANDIDATES.CONTENTID

Anyone know whats wrong? Below are the load script, table definitions and an example of inserting data with SQL.

Many thanks, Mark Johnson. markj_at_transilluminant.com

The load script:

---

LOAD DATA
INFILE *
INTO TABLE blog_load
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (entry_type, datetime, nonce, reportid,   candidates NESTED TABLE TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'     (contentid )
)
BEGINDATA
IM,"the time",nonce789,reportZXC,"cand1";"cand2" IM,"the time",nonceQWE,reportZXC,"cand3";"cand4"

Table defs:

---

create or replace type BLOG_CANDIDATES_TY as object ( CONTENTID VARCHAR2(20)
);

create or replace type BLOG_CANDIDATES_NT as table of BLOG_CANDIDATES_TY; create table BLOG_LOAD (

ENTRY_TYPE	 VARCHAR2(2),
DATETIME	 VARCHAR2(20),
NONCE		 VARCHAR2(40),
REPORTID	 VARCHAR2(10),
CANDIDATES	 BLOG_CANDIDATES_NT)

nested table CANDIDATES store as BLOG_CANDIDATES_NT_TAB;

create or replace type BLOG_CANDIDATES_TY as object ( CONTENTID VARCHAR2(20)
);

create or replace type BLOG_CANDIDATES_NT as table of BLOG_CANDIDATES_TY; drop table BLOG_LOAD;

create table BLOG_LOAD (

ENTRY_TYPE	 VARCHAR2(2),
DATETIME	 VARCHAR2(20),
NONCE		 VARCHAR2(40),
REPORTID	 VARCHAR2(10),
CANDIDATES	 BLOG_CANDIDATES_NT)

nested table CANDIDATES store as BLOG_CANDIDATES_NT_TAB;

Data inserts from SQL just fine:

---

insert into BLOG_LOAD
values ('CA', 'a date', 'nonce123', 'report456',

	   BLOG_CANDIDATES_NT(
  	     BLOG_CANDIDATES_TY('contentABC'),
  		 BLOG_CANDIDATES_TY('contentDEF')
	   )

);

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 20 1999 - 22:37:44 CDT