COLLECTION TYPE 변경 방법
=========================

collection type은 attribute나 column의 데이타 타입으로
사용된다. 이전에 정의한 collection type의 property를 변경해야
하는 경우가 종종 발생하게 되는데 그 작업이 간단하지 않다.

ALTER TYPE문은 spec이나 body를 재컴파일하거나, member를 추가
하는 경우에는 사용할 수 있지만 현재 collection의 property를
바꿀수는 없다. 만약 변경하려한다면 다음 에러가 날 것이다.

ORA-22319: type attribute information altered in ALTER TYPE

collection의 property 를 변경하기 위해서는 collection을 drop
한 후, 다시 만들어야 하는데 참조하는 오브젝트가 존재하는 경우
dependency 문제로 인해 collection type을 drop 할 수도 없다.

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

즉, Oracle 8i 이전에는 collection의 attribute를 변경하려면
parent table과 collection type을 모두 drop한 후, 재생성
해야만 했다.

그러나 Oracle 8i new feature인 ALTER TABLE DROP COLUM문을
이용하여 parent table을 drop하지 않고도 collection type의
attribute를 변경할 수 있게 되었다.

다음은 parent table을 drop하지 않고 collection (nested table)을
변경하는 예제이다.


SQL> /* object type 선언 */
SQL> create type object_type as object (
col1 number,
col2 varchar2(20));

SQL> /* nested table type 선언*/
SQL> create type object_ntable as table of object_type;

SQL> /* parent table 생성 */
SQL> create table master (
col1 number primary key,
col2_list object_ntable)
nested table col2_list store as master_col2;

SQL> insert into master values (1,
object_ntable(object_type(1,'1'),
object_type(1,'2'),
object_type(1,'3')));


SQL> insert into master values (2,
object_ntable(object_type(2,'1'),
object_type(2,'2'),
object_type(2,'3')));

SQL> commit;


------------------------------------------------------
1. 기존에 저장된 collection data를 임시 table에 저장.
------------------------------------------------------

SQL> create table temp_detail as
select m.col1 "MASTER_COL1", n.col1, n.col2
from master m, table(m.col2_list) n;

SQL> select * from temp_detail;

MASTER_COL1 COL1 COL2
----------- ---------- --------------------
1 1 1
1 1 2
1 1 3
2 2 1
2 2 2
2 2 3

------------------------------------------------------
2. parent table의 nested table column을 drop 하기
3. nested table type을 drop하기.
4. object type을 drop하기.
------------------------------------------------------

SQL> alter table master drop column col2_list;
SQL> drop type object_ntable;
SQL> drop type object_type;

------------------------------------------------------
5. attribute을 변경한 object type을 재생성하기
6. nested table type을 재생성하기.
------------------------------------------------------

SQL> create type object_type as object (
col1 number,
col2 varchar2(30));

SQL> create type object_ntable as table of object_type;

------------------------------------------------------
7. parent table에 nested table column을 추가하기.
------------------------------------------------------

SQL> alter table master add (col2_list object_ntable)
nested table col2_list store as master_col2;


----------------------------------------------------------
8. 임시 테이블에 저장했던 data를 nested table에 저장하기.
----------------------------------------------------------

SQL> update master m
set col2_list = (cast(multiset(
select col1, col2
from temp_detail
where master_col1 = m.col1)
as object_ntable))
where col1 in
(select distinct master_col1 from temp_detail);

SQL> select * from master;

COL1
----------
COL2_LIST(COL1, COL2)
------------------------------------------------------
1
OBJECT_NTABLE(OBJECT_TYPE(1, '1'), OBJECT_TYPE(1, '2'), OBJECT_TYPE(1, '3'))

2
OBJECT_NTABLE(OBJECT_TYPE(2, '1'), OBJECT_TYPE(2, '2'), OBJECT_TYPE(2, '3'))


* TABLE(), DROP COLUMN 절은 Oracle 8i new features 이다.