trouble loading data into varray object

From: Michael Steigman <steigman_at_mediaone.net>
Date: Wed, 14 Jul 1999 01:51:27 +0000
Message-ID: <378BED1F.48373826@mediaone.net>

Hi,

I'm hoping someone can help me out with this somewhat newbie question. I've created a table with a varray object in it - basically, to hold a set of id numbers which pertain to each record in the table. My problem is that I can't insert data into the table from either the sqlplus command line OR with sqlload. I've attempted to follow all the examples I've seen. I'm not sure if the problem is in my definition of the object type or in the method for loading the data. I included pieces from the files below. Can anyone help?

Thanks in advance.

Michael

%cat buildhcky_db.sql

<snip>
create type varr_small as varray(17) of number(4); create type varr_big as varray(6) of char(50);

--
-- Teams
--
create table teams (
	tid 		number	(3) not null, 
	name 		varchar2(20),
 	pids 		varr_small, 
	wins 		number	(2), 
	losses 		number	(2), 
	ties 		number	(2),
	points		number	(2),
  	goals_for 	number	(2), 
	goals_against 	number	(2), 
	season_id	char	(5),
  	division	varchar2(20), 
	sector_id	char	(3), 
	last_game 	varchar2(20));

create sequence tid start with 20 increment by 1; <snip>

%cat teams.ctl

load data
replace
into table teams
fields terminated by ","
optionally enclosed by "'"
(

	tid sequence,
	name,
	pids,
	wins,
	losses,
	ties,
	points,
	goals_for,
	goals_against,
	season_id,
	division,
	sector_id,
	last_game

)

%cat teams.dat

'Mr. Tux
Penguins',varr_small(1,2,3,4,5,6,7,8,9,10,11,12,13,14),3,4,0,6,30,29,S99,'CCHA Division',BOS,'Penguins 7, Tigers 4'

%cat teams.log

SQL*Loader: Release 8.0.5.1.0 - Production on Tue Jul 13 21:17:34 1999

Control File:   teams.ctl
Data File:      teams.dat
  Bad File:     teams.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table TEAMS, loaded from every logical record. Insert option in effect for this table: REPLACE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
NAME                                FIRST     *   ,  O(')
CHARACTER            
PIDS                                 NEXT     *   ,  O(')
CHARACTER            
WINS                                 NEXT     *   ,  O(')
CHARACTER            
LOSSES                               NEXT     *   ,  O(')
CHARACTER            
TIES                                 NEXT     *   ,  O(')
CHARACTER            
POINTS                               NEXT     *   ,  O(')
CHARACTER            
GOALS_FOR                            NEXT     *   ,  O(')
CHARACTER            
GOALS_AGAINST                        NEXT     *   ,  O(')
CHARACTER            
SEASON_ID                            NEXT     *   ,  O(')
CHARACTER            
DIVISION                             NEXT     *   ,  O(')
CHARACTER            
SECTOR_ID                            NEXT     *   ,  O(')
CHARACTER            
LAST_GAME                            NEXT     *   ,  O(')
CHARACTER            

TID                            SEQUENCE (1, 1)

Record 1: Rejected - Error on table TEAMS, column PIDS. ORA-00932: inconsistent datatypes

Table TEAMS:
  0 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.   0 Rows not loaded because all fields were null.

Space allocated for bind array:                  65499 bytes(21 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Tue Jul 13 21:17:34 1999
Run ended on Tue Jul 13 21:17:34 1999

Elapsed time was:     00:00:00.16
CPU time was:         00:00:00.02

Received on Tue Jul 13 1999 - 20:51:27 CDT