Home » SQL & PL/SQL » SQL & PL/SQL » Modifying custom types that are payloads on AQ (10g)
Modifying custom types that are payloads on AQ [message #289267] Thu, 20 December 2007 08:33 Go to next message
Mike Nibeck
Messages: 49
Registered: May 2002
Member
Situation:

- Series of complex nested custom types
- The top level object is defined as the payload types on an advanced queue

Now everything works fine until I need to make a change to any of the type definitions. "CREATE OR REPLACE TYPE". I get the following:

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

1 - If I try to edit any of the parent nodes, I am not allowed because it has dependent children.
2 - I ultimately can't make any type change because the AQ creates a dependency on the top level object

I currently end up dropping the entire queue and all objects and then re-creating everything.

Is there anyway I can somehow temporarily disable the link between the AQ and the object definition so I can modify the object without dropping the queue first?

_mike
Re: Modifying custom types that are payloads on AQ [message #289377 is a reply to message #289267] Fri, 21 December 2007 00:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use a forced drop:
SQL> create or replace type a_child as object
  2  (
  3  a_variable varchar2(20)
  4  )
  5  /

Type created.

SQL> create or replace type a_parent as object
  2  (
  3  a_variable a_child
  4  )
  5  /

Type created.

SQL> create or replace type a_child as object
  2  (
  3  a_variable varchar2(20)
  4  , another_var varchar2(20)
  5  )
  6  /
create or replace type a_child as object
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


SQL> drop type a_child force;

Type dropped.

SQL> create or replace type a_child as object
  2  (
  3  a_variable varchar2(20)
  4  , another_var varchar2(20)
  5  )
  6  /

Type created.

SQL> alter type a_parent compile;

Type altered.
Re: Modifying custom types that are payloads on AQ [message #384142 is a reply to message #289267] Mon, 02 February 2009 05:40 Go to previous messageGo to next message
du-it
Messages: 2
Registered: February 2009
Location: Berlin - Germany
Junior Member
I have the same problem. Using a forced drop works well when working with a PL*SQL Plus Worksheet. But when I try to debug in an IDE (eg. Oracle SQL Developer) I ever get the error message again. The IDE seems to try to recompile every time but does not do a forced drop before. Hence, how can I use custom types which references another custom type (or have dependencies to other custom types) within an IDE??

Thank you,
Dirk
Re: Modifying custom types that are payloads on AQ [message #384647 is a reply to message #384142] Wed, 04 February 2009 11:16 Go to previous message
du-it
Messages: 2
Registered: February 2009
Location: Berlin - Germany
Junior Member
Oracle SQL Developer provides an option for custom types to "compile invalid".
Previous Topic: To find out a column value is updated or not? (merged 6)
Next Topic: Cut data into groups
Goto Forum:
  


Current Time: Fri Dec 09 01:41:29 CST 2016

Total time taken to generate the page: 0.13536 seconds