Home » SQL & PL/SQL » SQL & PL/SQL » Inheritance & nested tables (Enterprise 10.2.0.1)
Inheritance & nested tables [message #307763] Wed, 19 March 2008 18:01 Go to next message
peeterek
Messages: 9
Registered: March 2008
Junior Member
Hi,

Let's consider that code:


create or replace type A as table of varchar(10);

create or replace type B as object (
id_B number(1),
zm_B A
)

--now, let's create some C object and its subtype.

create or replace type C as object (
id_C number(1)
)NOT FINALL;

create or replace type D under C (
id_D number(1),
zm_D B
)


To sum up:

Subtype D extends C. In our C type zm_D appears.
zm_D is of B type which is NOT a nested one but
type B contains a zm_B whitch is a nested table of varchar2(10)

My question is how to create object table of Cs

I've tried something like:
create table tmp_tbl of C
nested table zm_D.zm_B store as some_tbl

but it didn't work.


Thank you for any advices,

best regards,
Peeterek
Re: Inheritance & nested tables [message #307770 is a reply to message #307763] Wed, 19 March 2008 18:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Object Oriented Oracle?
Results 1 - 10 of about 6,950 for "object oriented oracle"
GOOGLE is your friend, but only when you actually use it.
Re: Inheritance & nested tables [message #307784 is a reply to message #307763] Wed, 19 March 2008 20:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I think you want to create tmp_tbl of D, not C, don't you?

SCOTT@orcl_11g> create or replace type A as table of varchar(10);
  2  /

Type created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> create or replace type B as object (
  2  id_B number(1),
  3  zm_B A
  4  );
  5  /

Type created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> create or replace type C as object (
  2  id_C number(1)
  3  )NOT FINAL;
  4  /

Type created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> create or replace type D under C (
  2  id_D number(1),
  3  zm_D B
  4  );
  5  /

Type created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> 
SCOTT@orcl_11g> desc a
 a TABLE OF VARCHAR2(10)

SCOTT@orcl_11g> desc b
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_B                                               NUMBER(1)
 ZM_B                                               A

SCOTT@orcl_11g> desc c
 c is NOT FINAL
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_C                                               NUMBER(1)

SCOTT@orcl_11g> desc d
 d extends SCOTT.C
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_C                                               NUMBER(1)
 ID_D                                               NUMBER(1)
 ZM_D                                               B

SCOTT@orcl_11g> 
SCOTT@orcl_11g> create table tmp_tbl of d
  2  nested table zm_d.zm_b store as some_tbl
  3  /

Table created.

SCOTT@orcl_11g> desc tmp_tbl
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_C                                               NUMBER(1)
 ID_D                                               NUMBER(1)
 ZM_D                                               B

SCOTT@orcl_11g> insert into tmp_tbl values (1, 2, b(3, a(4)))
  2  /

1 row created.

SCOTT@orcl_11g> select * from tmp_tbl
  2  /

      ID_C       ID_D
---------- ----------
ZM_D(ID_B, ZM_B)
----------------------------------------------------------------------------------------------------
         1          2
B(3, A('4'))


SCOTT@orcl_11g> 

[Updated on: Wed, 19 March 2008 20:41]

Report message to a moderator

Re: Inheritance & nested tables [message #307923 is a reply to message #307784] Thu, 20 March 2008 06:18 Go to previous messageGo to next message
peeterek
Messages: 9
Registered: March 2008
Junior Member
Quote:
SCOTT@orcl_11g>
SCOTT@orcl_11g> create table tmp_tbl of d
2 nested table zm_d.zm_b store as some_tbl
3 /

Table created.

Yes, this is corect - thank you but it is not what I was asking for.

I want to create object table of Cs! so I can perform polymorphic functions.

If type D had not field that contain nested table the problem would have been easy but it has.

consider this:

create or replace type C as object (
id_CC number(1),
MEMBER FUNCTION SHOW RETURN VARCHAR2
)NOT FINAL;

CREATE OR REPLACE TYPE BODY C AS
  MEMBER FUNCTION show RETURN VARCHAR2 IS
  BEGIN
    RETURN 'object C';
  END;
END;

create or replace type D under C (
id_D number(1),
zm_D B,
OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2
);

CREATE OR REPLACE TYPE BODY D AS
  OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS
  BEGIN
    RETURN 'object D';
  END;
END;


Now we need a table of Cs to be able to put both c and d types objects into tmp_tbl and to execute polymorphic functions like:

SELECT p.show() FROM tmp_tbl p

Re: Inheritance & nested tables [message #307974 is a reply to message #307923] Thu, 20 March 2008 11:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> create or replace type A as table of varchar(10);
  2  /

Type created.

SCOTT@orcl_11g> create or replace type B as object (
  2  id_B number(1),
  3  zm_B A
  4  );
  5  /

Type created.

SCOTT@orcl_11g> create or replace type C as object (
  2  id_CC number(1),
  3  MEMBER FUNCTION SHOW RETURN VARCHAR2
  4  )NOT FINAL;
  5  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE BODY C AS
  2    MEMBER FUNCTION show RETURN VARCHAR2 IS
  3    BEGIN
  4  	 RETURN 'object C';
  5    END;
  6  END;
  7  /

Type body created.

SCOTT@orcl_11g> create or replace type D under C (
  2  id_D number(1),
  3  zm_D B,
  4  OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2
  5  );
  6  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE BODY D AS
  2    OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS
  3    BEGIN
  4  	 RETURN 'object D';
  5    END;
  6  END;
  7  /

Type body created.

SCOTT@orcl_11g> create table tmp_tbl (
  2  zm_C C)
  3  /

Table created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl (zm_c) VALUES (c (1))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl (zm_c) VALUES (d (1, 2, b(3, a(4))))
  2  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM tmp_tbl
  2  /

ZM_C(ID_CC)
----------------------------------------------------------------------------------------------------
C(1)
D(1, 2, B(3, A('4')))

SCOTT@orcl_11g> SELECT p.zm_c.show() FROM tmp_tbl p
  2  /

P.ZM_C.SHOW()
----------------------------------------------------------------------------------------------------
object C
object D

SCOTT@orcl_11g> 

Re: Inheritance & nested tables [message #307979 is a reply to message #307974] Thu, 20 March 2008 11:45 Go to previous messageGo to next message
peeterek
Messages: 9
Registered: March 2008
Junior Member
thank you very much Smile


I have got one more question.

I understand that in exaple above I have a table of objects.
Is there a way to solve problem and create object table???
Re: Inheritance & nested tables [message #308015 is a reply to message #307979] Thu, 20 March 2008 15:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I don't think you can use the syntax:

SCOTT@orcl_11g> create table tmp_tbl of c
  2  /
create table tmp_tbl of c
*
ERROR at line 1:
ORA-22913: must specify table name for nested table column or attribute


because it will require the nested table storage clause, but I can't seem to find any combination of aliases and such for an identifier that it will accept either:

SCOTT@orcl_11g> create table tmp_tbl of c
  2  nested table zm_d.zm_b store as some_tbl
  3  /
nested table zm_d.zm_b store as some_tbl
             *
ERROR at line 2:
ORA-00904: : invalid identifier


However, you can use the syntax:

SCOTT@orcl_11g> create or replace type tmp_tbl as table of c;
  2  /

Type created.


without any storage clause.

Perhaps someone else can find an acceptable nested table storage clause. I tried various combinations and finally gave up.


Re: Inheritance & nested tables [message #308132 is a reply to message #308015] Fri, 21 March 2008 07:02 Go to previous message
peeterek
Messages: 9
Registered: March 2008
Junior Member
ok, thank you for your efforts:)
Previous Topic: Tablespace of IOT table
Next Topic: ORA-00904: invalid column name--HELP! query is working in pl/sqldev tool but not in shell script
Goto Forum:
  


Current Time: Sat Dec 10 05:17:56 CST 2016

Total time taken to generate the page: 0.07917 seconds