how to modify an object type

From: steve noyle <stevenoyle1_at_yahoo.com>
Date: Wed, 29 Sep 2004 21:10:37 -0700 (PDT)
Message-ID: <20040930041037.21779.qmail@web51002.mail.yahoo.com>

hi, I would like to verify the proper way of adding an attribute to an object type.

>From the 9i documentation, it appears that I can just
add an attribute directly to an object from the 'create type' syntax but there were a number of notes on metalink that advise to drop all dependent objects and recreating the object type. The dependent tables are huge so I cannot afford the time to drop and recreate them.

Can you verify what would be the proper procedure of adding an attribute to an object type?

SQL> create type mytype as object (name varchar2(50));   2 /
Type created.

SQL> create table mytab (x number, y mytype);  

Table created.

SQL> create or replace type mytype as object (name varchar2(50), address varchar2(50));
  2 /
create or replace type mytype as object (name varchar2(50), address varchar2(50));
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

SQL> alter type mytype add attribute (address varchar2(50)) cascade;  

Type altered.  

SQL> desc mytype

Name                Null?    Type
------------------- -------- --------- 
NAME                         VARCHAR2(50)
ADDRESS                      VARCHAR2(50)
 

Also, what would be the query to list all the dependent objects? I was not able to find it in user_object_tables.

SQL> select * from user_object_tables;  

no rows selected

TIA Steve

---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 29 2004 - 23:06:30 CDT