• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2852
  • Last Modified:

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

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
royjayd
Asked:
royjayd
  • 6
  • 5
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
royjaydAuthor Commented:
Ok. Any idea what the solution should be?

Thanks!
0
 
slightwv (䄆 Netminder) Commented:
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
Technology Partners: 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!

 
royjaydAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
royjaydAuthor Commented:
>>>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
 
slightwv (䄆 Netminder) Commented:
For what you are doing there is no difference between char(1) and varchar2(1).
0
 
royjaydAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
royjaydAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now