Solved

Error: ORA-02303: cannot drop or replace a type with type or table dependents

Posted on 2012-04-08
11
2,531 Views
Last Modified: 2012-04-09
hi guys

I am using oracle sql developer

Under Types i have something defined like this


create or replace TYPE custom_person_map AS OBJECT
    ( id   NUMBER    
    , name        VARCHAR2(19)    
    , active            CHAR(1)
    , crtd_by           VARCHAR2(60)    
    , lst_updt_by       VARCHAR2(60)    
    );


now if i am trying to replace this line
active            CHAR(1)
 with
active            varchar2(1)

i get an error
Error: ORA-02303: cannot drop or replace a type with type or table dependents


Anyidea what this is and what the solution could be?

Any help would be greatly appreciated.

thanks
0
Comment
Question by:royjayd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
ID: 37823030
The error is self-explanitory:  with dependents


This type is in use in some other object.

select * from user_dependencies where referenced_name='CUSTOM_PERSON_MAP'
0
 

Author Comment

by:royjayd
ID: 37823055
Ok. Any idea what the solution should be?

Thanks!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823221
Have you looked up that errorcode?
http://ora-02303.ora-code.com/

The solutons are there...

If the type is used in a TABLE, then you need to drop the table first.  If it is used in another object, FORCE might work.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:royjayd
ID: 37823251
well i ran the query
>>select * from user_dependencies where referenced_name='CUSTOM_PERSON_MAP'

and it says >> no rows selected.

I think i know what the problem is

In the database table ..active      is defined as  varchar2(1)  
but i have defined it as CHAR(1)   in the TYPE custom_person_map.

Is that a problem ? Can varchar2  and char be used interchangibly?

thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823265
>>and it says >> no rows selected.

Try ALL_DEPENDENCIES or better if you have permission DBA_DEPENDENCIES.  If you get a ORA-02303, there is a dependent object somewhere.

>>Is that a problem ? Can varchar2  and char be used interchangibly?

For the most part, yes.  For what you are trying to do: yes as well.
0
 

Author Comment

by:royjayd
ID: 37823331
>>>For the most part, yes.  For what you are trying to do: yes as well.


Are you saying its a problem or are you saying it can be used interchangibly?


thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823390
For what you are doing there is no difference between char(1) and varchar2(1).
0
 

Author Comment

by:royjayd
ID: 37823440
ok...thx. so that is not the issue like i was suspecting. I was however able to drop the Type and create it again.

The problem is the store proc fails and this is what i see in console

Caused by: java.sql.SQLException: Internal Error: Inconsistent catalog view      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229)
      at oracle.sql.StructDescriptor.initMetaData1_9_0(StructDescriptor.java:1465)
      at oracle.sql.StructDescriptor.initMetaData1(StructDescriptor.java:1316)
      at oracle.sql.StructDescriptor.isInstantiable(StructDescriptor.java:865)
      at oracle.sql.STRUCT.<init>(STRUCT.java:147)
      at main.com.dao.PersonMappingStoreProc$1.setTypeValue_aroundBody0(PersonMappingStoreProc.java:117)
        

not sure what the problem is

thanks.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823465
>>The problem is the store proc fails and this is what i see in console

Is this a 'new' error or were you attempting to change the TYPE in an attempt to 'fix' the error?
0
 

Author Comment

by:royjayd
ID: 37823484
This is the original error(Inconsistent catalog view) which i was getting and i was suspecting the problem is with the TYPE (changing char to varchar2). But even after i changed the TYPE it gives me the same error...Inconsistent catalog view.

Been trying to figure what it means last two days. any help will be appreciated..
thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823506
>>This is the original error(Inconsistent catalog view) which i was getting and i was suspecting the problem is with the TYPE

This is a different question and needs to be asked as one.  This question was to address the ORA-02303

That said, a quick Google mentiones a newer class fixed the issue:
http://www.orafaq.com/forum/t/35142/2/
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question