Nested tables and OOP realization in Oracle

Hello!

I'm quite confused with Oracle's understanding of OOP and don't
understand following situation: I have supertype h1 and tbl_h1 as
table of h1.
Then I have created absolutely unrelated type row_text and
tbl_row_text as table of row_text.
Then I create type h2 UNDER type h1 with column of  tbl_row_text.

Now if I create a table with column of tbl_h1 i.e. normal table with
column as nested table, I actually got 2 nested tables. One for
tbl_h1, which I fully expected. Second with system generated name for
h2 as subtype of h1. WHYYYY? Even more - as soon as I create another
subtype h3 under h1 I got another hidden nested table.

I was under the impression that if I create instance of an object of
supertype, it doesn't care of possibility that this supertype might
have other subtypes. It seems that Oracle thinks another way. And now
I don't know whether my understanding of OOP is broken, or Oracle's???

If anyone is wondering why do I care - the problem actually is that
our developers have used such constructions and now I have problems
with data pump import for these hidden tables, i.e., Oracle cannot
create them even if all types are precreated. And yes I  said - don't
use nested tables, unfortunately they were already using them before
:(

OK here is the precise example with some comments:
-- creating supertype h1 and table of it
SQL> create or replace type h1 as object (a number)
  2  not final;
  3  /

Type created.

SQL> create or replace type tbl_h1 as table of h1;
  2  /

Type created.

-- creating an unrelated type and table of it
SQL> create or replace type row_text as object (
  2    txt varchar2(100))
  3  not final;
  4  /

Type created.

SQL> create or replace type tbl_row_text as table of row_text;
  2  /

Type created.

-- creating subtype h2 under supertype h1
SQL> create or replace type h2 under h1 (some_texts tbl_row_text);
  2  /

Type created.

-- creating table with column b type as table of h1
-- explicitly defining that there will be nested table tab1_nested
SQL> create table tab1 (a number, b tbl_h1)
  2  nested table b
  3  store as tab1_nested;

Table created.

-- how many nested tables we got??? - yes 2!
SQL> select table_name, parent_table_name, parent_table_column
  2  from user_nested_tables;

TABLE_NAME                     PARENT_TABLE_NAME
------------------------------ ------------------------------
PARENT_TABLE_COLUMN
---------------------------------------------------------------------
SYSNTfsl/+pzu3+jgQAB/AQB27g==  TAB1_NESTED
TREAT(SYS_NC_ROWINFO$ AS "GINTS"."H2")."SOME_TEXTS"

TAB1_NESTED                    TAB1
B

-- now let's craete another subtype h3 under h1
SQL> create or replace type h3 under h1 (some_texts tbl_row_text);
  2  /

Type created.

-- another nested table appears!!!
SQL> select table_name, parent_table_name, parent_table_column
  2  from user_nested_tables;

TABLE_NAME                     PARENT_TABLE_NAME
------------------------------ ------------------------------
PARENT_TABLE_COLUMN
---------------------------------------------------------------------
SYSNTfsl/+pzu3+jgQAB/AQB27g==  TAB1_NESTED
TREAT(SYS_NC_ROWINFO$ AS "GINTS"."H2")."SOME_TEXTS"

SYSNTfsl/+pz03+jgQAB/AQB27g==  TAB1_NESTED
TREAT(SYS_NC_ROWINFO$ AS "GINTS"."H3")."SOME_TEXTS"

TAB1_NESTED                    TAB1
B

-- let's try to describe it
SQL> desc "SYSNTfsl/+pzu3+jgQAB/AQB27g=="
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 TXT                                                VARCHAR2(100)

Gints Plivna
http://www.gplivna.eu
--
http://www.freelists.org/webpage/oracle-l

Other related posts:

  • » Nested tables and OOP realization in Oracle - Gints Plivna