Re: Inserting into nested object lists

The problem here (probably) is that
the nested table column is null.

Before you can use this syntax, you
have to have (at least) an empty nested
table in the nested table column -

e.g the following fails:
insert into demo_tab(pk_col, nest_col) values (1, null); insert into THE (
  SELECT c.nest_col
   FROM demo_tab c
   WHERE c.pk_col = 1
  )
values( object_type('abc','ABC','intermediate')) ;

but the following should work (typos excepted).

insert into demo_tab(pk_col, nest_col) values (1, object_type()); insert into THE (
  SELECT c.nest_col
   FROM demo_tab c
   WHERE c.pk_col = 1
  )
values( object_type('abc','ABC','intermediate')) ;

Are you still on 8.0 ? If you have migrated to 8.1, you should change the THE() into the more intuitive TABLE() - the() is maintained for backwards compatibility only.

(See chapter 16 Collection Objects in my soon-to-be available book).

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



eric_levin_at_my-deja.com wrote in message <8vhff1$acu$1_at_nnrp1.deja.com>...


>create type obect_type as (

>colum_one       varchar(80)

>column_two      varchar(80)

>column_three   varchar(80)

>)

>

>create type object_type_list as table of object_type

>

>then we used the object_type_list as a colum in a table We would like to

>insert values into  this object for certain records in the table. The

>following did not work? Does anyone have any suggestions?

>

>insert into THE (

>  SELECT  c.table_column_name

>   FROM   table c

>   WHERE  c.primary_key = 3

>  )

>values( object_type('abc','ABC','intermediate'))

>

>Error message:  ORA-22908: reference to NULL table value

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.