Re: Nested Tables

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/04
Message-ID: <3486ce5b.2058369@inet16>#1/1

On Tue, 02 Dec 1997 16:13:10 -0600, Brent Collins <brent_at_ti.com> wrote:

>I have a schema which uses nested tables to store semiconductor
>equipment
>processing data. I was using an SQL statement to insert the parent row
>data
>and nested table data in one call. I ran into a limit of 999 nested
>table rows.

You don't mention what environment/OS/language you are using but..... Here is an example that uses one insert to insert lots more the 999 rows into a nested table and shows how to construct the empty nested table to be inserted into later. Hope this helps (run on solaris with 8.0.3)

SQL> create type ntType as table of number;   2 /  

Type created.  

SQL>
SQL> create table ntTest

  2  (       pkdata  number primary key,
  3          ntData  ntType

  4 )
  5 nested table ntData store as ntTest_ntData   6 /  

Table created.  

SQL> REM do everything as one big insert, insert the parent and all SQL> REM nested table rows in one insert.

SQL> insert into ntTest
  2 select 1,
  3 cast( multiset( select object_id from all_objects ) as ntType )   4 from dual;  

1 row created.  

SQL> REM Now, see how many nested table rows we added

SQL> select count(*) from the( select ntData from ntTest where pkdata = 1 );  

  COUNT(*)

---

      8573  

SQL> REM now, insert the parent and an EMPTY nested table

SQL> insert into ntTest values ( 2, ntType() );  

1 row created.  

SQL> REM Now, for that parent, populate the nested table

SQL> insert into the ( select ntData from ntTest where pkdata = 2 )   2 select object_id from all_objects
  3 /  

8573 rows created.  

SQL> select count(*) from the( select ntData from ntTest where pkdata = 2 );  

  COUNT(*)

---

      8573  

>Since I sometimes need to insert more than 999 rows, I decided to try to
>insert
>the parent row and nested table rows separately. I receive error ORA
>22908
>"Reference to NULL table value" when trying to add the nested table
>rows. I
>found some documentation which said to ensure that an empty nested table
>
>instance is created using and empty nested table constructor.
>
>Does anyone know how to create this empty nested table instance?
>
>Regards,
>Brent Collins
>Texas Instruments
>IT Services
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Dec 04 1997 - 00:00:00 CST