plsqlplsqldeveloperoracle19cobject-type

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


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;
...

Solution

  • 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.