Hi,
The error message is quite self-explanatory - as source and destination column have different data types, it is not possible to convert its value. You have to convert it explicitly. Of course it would not happen if the table contained multiple columns with Oracle built-in data types instead of one user-defined object one.
It would also be helpful, if you posted a test case here as well - DDL and DML statements for re-creation your environment.
Anyway, here they are:
Code:
create type obj_contact1 as object ( int integer, str varchar2(50) );
/
create table table01 ( office_details obj_contact1 );
insert into table01( office_details )
select obj_contact1( 1, 'a' ) from dual;
create type obj_contact2 as object ( int integer, str varchar2(50) );
/
create table table02 ( office_details obj_contact2 );
I was too lazy to cope with two schemas, so I created all in one schema, just distinguished data type names (as tables already have different names).
As you did not post definition of OBJ_CONTACT data type, I did deduce it as simple OBJECT type with two attributes. I also suppose that it has the same definition in both schemas.
It would be nice if you posted this in your initial post to save some work to the ones who try to help you and prevent doubts about your exact configuration.
In the end (if my assumptions are correct), the required statement as simple as:
Code:
insert into table02( office_details )
select obj_contact2( table01.office_details.int, table01.office_details.str )
from table01 table01;
dBforums
>
how to synchronize two different user defined data types in two different schemas
02-13-12, 01:13
how to synchronize two different user defined data types in two different schemas
Linear Mode
