RE: how to modify an object type

From: Srinivas T <SrinivasT_at_KPITCummins.com>
Date: Thu, 30 Sep 2004 09:52:13 +0530
Message-ID: <4A1BE23A7B777442B60F4B4916AE0F130292FE1C@sohm.kpit.com>

Hi friends,

I am getting the following error after altering the type...

SQL> desc mytype
ERROR:
ORA-22335: The client cannot work with an altered type Suggest regarding the same.....

Regards,
Srinivas

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of steve noyle Sent: Thursday, September 30, 2004 9:41 AM To: oracle-l_at_freelists.org
Subject: how to modify an object type

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

>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.=20

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

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

SQL> create table mytab (x number, y mytype); =20
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;
=20
Type altered.
=20
SQL> desc mytype

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

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

SQL> select * from user_object_tables;
=20
no rows selected

TIA Steve

---

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

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

Received on Wed Sep 29 2004 - 23:24:02 CDT