I have a queue and using an user defined type as payload,
example: create or replace type order_det as object(order_id number, order_desc varchar(50),order_qty number) ;
Now, I have a requirement to add new attribute to the payload. I have tried the following, not sure if this the right approach. Please let me know if there is any other way to achieve this.
1. Create a new user defined type
example: create or replace type order_det_new as object(order_id number, order_desc varchar(50),order_qty number) ;
2. Create a new queue table using the new payload type (order_det_new)
3. Stop the old queue
4. copy the data from old queue table to new queue table (migrate the data)
5. Drop the old queue
6. Create a new queue with same name as old queue
7. Modify enqueue/dequeue codes to use new payload
8. start the new queue.
The data at the old queue still holds the old payload type , so copy of data from old queue may not work.
Here is my suggestion.
1) Stop Enqueue to the Old queue table.
2) Make sure that there are no more outstanding messages.
3) Drop the queue
4) Create a new type.
5) Create new Queue Table/ queue
6) Enable the application for enqueue.
Test this in your Development Environment.