Exporting nested tables

From: Louis Avrami <avramil_at_concentric.net>
Date: Tue, 5 Sep 2000 11:44:32 -0700
Message-Id: <10610.116236@fatcity.com>

Hello all,

We're running Solaris 2.7, RDBMS 8.1.6.2. We have one schema, part of a purchased application, that makes use of "nested tables". When trying to export this schema, errors occur on these objects.

We have several objects created by the following method: Creating Table 'BUCKET'
SQL> CREATE TABLE BUCKET OF BUCKET_T (
  2 id PRIMARY KEY )
  3 OBJECT IDENTIFIER IS PRIMARY KEY
  4 NESTED TABLE FIELDS STORE AS BUCKET_NT10   5 -- RETURN AS LOCATOR
  6 ,
  7 NESTED TABLE PROPERTIES STORE AS BUCKET_NT20   8 -- RETURN AS LOCATOR
  9 PARALLEL
10 /

Table created.
SQL> ALTER TABLE BUCKET_NT10 add( SCOPE FOR (column_value) IS FIELD )
2 /

Table altered.

A description of the BUCKET_NT10 object shows that it references FIELDS:

SQL> desc BUCKET_NT10                                       
                  
 Name                        Null?    Type
 --------------------------- -------- ---------------
 COLUMN_VALUE                         REF OF FIELD_T

The BUCKET_NT10 object seems to be identified as a TABLE in the data dictionary:

SQL> select object_type
2 from user_objects
3 where object_name = 'BUCKET_NT10';

OBJECT_TYPE

TABLE It is indicated as a nested table in USER_TABLES:

SQL> select NESTED
2 from user_tables
3 where table_name = 'BUCKET_NT10';

NES
---

YES When trying to export the schema that owns these objects, we receive the following errors:

. . exporting table                    BUCKET_NT10

EXP-00056: ORACLE error 1007 encountered ORA-01007: variable not in select list

I believe that this is Oracle bug 947083, which supposedly is fixed in 8.1.7.

Right now I'm kind of locked into getting this schema out of the database via some sort of utility like exp. The vendor has been doing extensive customizations which may not be reproducible if the schema objects had to be recreated in another database. We need to move this schema to our production environment from its current location.

Is there anyone out there who has experienced this problem and found some sort of workaround?

Anyone have a 8.1.7 database with nested tables? Does exp work? Oracle Support said that they tested for this problem with 8.1.6.2 and exp worked. Oops. Supposedly this bug is fixed in 8.1.7, but now I don't believe it.

Could there be some problem with how these nested tables are created? I'll have to go back and check the syntax, but they seem to create ok, and the application appears to be working.

Any info or suggestions would be greatly appreciated.

Thanks,

Lou Avrami

Sent from a WebBox - http://www.webbox.com FREE Web based Email, Files, Bookmarks, Calendar, People and Received on Tue Sep 05 2000 - 13:44:32 CDT