How to assign values of object type attributes to different object type with same attribute properties in plsql?

Question

I have two different types with same attributes. I need to assign the values of the first type's attributes to other one. They are exactly the same apart from the schemas and object names.

CREATE OR REPLACE TYPE SCHEMA_A.type_A AS OBJECT(XCOL VARCHAR2(80), YCOL VARCHAR2(80), ZCOL CHAR(2));

CREATE OR REPLACE TYPE SCHEMA_B.type_B AS OBJECT(XCOL VARCHAR2(80), YCOL VARCHAR2(80), ZCOL CHAR(2));

I can assign values one by one by hand like below but reality there are over 80 attributes. Is there a more elegant way to achive the same effect?

SCHEMA_A.type_A.XCOL := SCHEMA_B.type_B.XCOL;
SCHEMA_A.type_A.YCOL := SCHEMA_B.type_B.YCOL;
...

Answer 1

AFAIK You are pretty much stuck with with attribute-by-attribute assignment. That is a by-product of creating 2 type the same - that is your major error. But there a 2 possible solutions:

1. Create a common schema with just 1 type then update the references to the common schema.
2. Write a function that takes the 2 types as parameters, source and destination, and does the attribute-by-attribute copy. Your code then just calls the function.

Answer 2

As mentioned in his answer @Belayer, the best solution would be to create a common data type for both schemes.

---

The quick and dirty solution is to convert using an SQL query. Check out the following reproducible example:

create or replace type A.objA as object (attr1 varchar2 (8), attr2 int, attr3 date)
/
create or replace type B.objB as object (attr1 varchar2 (8), attr2 int, attr3 date);
/
create or replace type A.objtA as table of A.objA;
/
create or replace type A.objtB as table of B.objB;
/
var rc refcursor 
declare 
    a A.objA;  
    b B.objB := B.objB ('name B', 1, trunc (sysdate));
    function cast (o B.objB) return A.objA is
        t A.objtA;
    begin
        select cast (multiset (select * from A.objtB (b)) 
                 as A.objtA) into t from dual; 
        return t(1);
    end;
begin 
    a := cast (b);
    open :rc for select a a from dual;
end;
/

A(ATTR1, ATTR2, ATTR3)
------------------------------------------------
OBJA('name B', 1, '2020-10-18 00:00:00')

Answer 3

Since the types are schema objects they have default constructors that can slightly simplify assignment:

declare
    v_type_a type_a := type_a('a', 'a', 'a');
    v_type_b type_b := type_b('b', 'b', 'b');
begin
    v_type_a := type_a(v_type_b.xcol, v_type_b.ycol, v_type_b.zcol);
end;
/

In 18c and above, the same idea will work even for PL/SQL-only types because of the new qualified expression feature.

If there are only two types, then the above shortcut is probably your best bet. Let us know if you have multiple sets of types that need to be assigned; in that case, it might be worth spending the extra effort to build a solution using ANYDATA or or using the data dictionary to dynamically build functions.