The application which we developed has one schema, USER1, which has tables, types, packages etc.
I have to move some of the user-defined types to a different schema, USER2. USER1 has few tables which have a dependency on these user-defined types. We recreated the types in USER2, but before I drop the types in USER1, we have to remove the table's dependency on them. To do this, I am thinking of using DBMS_REDEFINITION by creating an intermediate table using the user defined type in USER2 (instead of USER1)
I'm using the following to cast the user defined type in USER1 and to the new one created in USER2, where EMP_LIST is an array of EMP_TYPE.
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE( 'user1', 'department', 'department_in' , 'dept_id dept_id, dept_name dept_name , cast(emp_list as user2.emp_type) emp_list' , dbms_redefinition.cons_use_rowid); END;
But, I'm getting the following error:
Error : ORA-22907: invalid CAST to a type that is not a nested table or VARRAY
I tried using the MULTISET operator but it looks as though it's not allowed. How could we achieve this?