cannot insert into table w/ varray

From: Michael Steigman <steigman_at_mediaone.net>
Date: Wed, 14 Jul 1999 12:26:00 +0000
Message-ID: <378C81D8.BF8C9807@mediaone.net>

Hi,

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

(c) Copyright 1998 Oracle Corporation. All rights reserved.

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 Wed Jul 14 1999 - 07:26:00 CDT