Re: Oracle8 nested table flatten query

From: <si_bendovi_at_hotmail.com>
Date: Fri, 12 Mar 1999 22:12:26 GMT
Message-ID: <7cc3g1$5hs$1@nnrp1.dejanews.com>

Thank you for help. I could not flatten more than one record in table containing nested table, but query by nested table is possible:

SQL> select b from b b1 where 5 < ( select sum( b3.a) from the ( select b from b b2 where b1.id = b2.id) b3);

B(A, B)

-------------------- TB(TA(1, 1), TA(2, 2), TA(3, 3)) TB(TA(1, 1), TA(2, 2), TA(3, 3), TA(4, 4))

How can I inherit type? Have a look to a code below. I mean create type tc with the same structure like ta and member functions, but one column more. How can I modify type structure if it is instantioned by table? I mean there is at least one table using this type type.

SQL> desc ta

 Name                            Null?    Type
 ------------------------------- -------- ----
 A                                        NUMBER
 B                                        NUMBER

METHOD

MEMBER FUNCTION AB RETURNS NUMBER SQL> create or replace type tc as ta ( c number); 2 /
create or replace type tc as ta ( c number); *
ERROR at line 1:

ORA-06545: PL/SQL: compilation error - compilation aborted
ORA-06550: line 1, column 12:
PLS-00103: Encountered the symbol "TA" when expecting one of the following:

array VARRAY_ table object FIXED_ varying ORA-06550: line 0, column 0:
PLS-00565: TC must be completed as a potential REF target (object type)

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Mar 12 1999 - 16:12:26 CST