Nested tables and OOP realization in Oracle
Date: Thu, 4 Feb 2010 19:10:06 +0200
Message-ID: <6e49b6d01002040910u35c4e13dvebdc88e8b9857a33_at_mail.gmail.com>
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_NAMEPARENT_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_NAMEPARENT_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)
http://www.gplivna.eu
--
http://www.freelists.org/webpage/oracle-l Received on Thu Feb 04 2010 - 11:10:06 CST