Oracle change column datatype from varray

Question

In Oracle database I have two schemas with two tables that mirror each other, except for one column which in both tables is varray of numbers, but in schema A it's A.VARRAY, and in schema B it's B.VARRAY.

As a result I'm unable to migrate data between tables, since they have inconsistent datatypes.

Is there any way to change column datatype from A.varray to B.varray without losing data?

Answer 1

Just use cast(col as B.VARRAYTYPE):

SQL> CREATE OR REPLACE TYPE V_TYPE_1 AS VARRAY(5000) OF NUMBER(1);
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE V_TYPE_2 AS VARRAY(5000) OF NUMBER(1);
  2  /

Type created.

SQL> create table t1 (a V_TYPE_1);

Table created.

SQL> insert into t1 values(v_type_1(1,2,3,4,5));

1 row created.

SQL> create table t2 (a V_TYPE_2);

Table created.

SQL> insert into t2 select cast(a as v_type_2) from t1;

1 row created.

SQL> select * from t2;

A
-------------------------
V_TYPE_2(1, 2, 3, 4, 5)

1 row selected.

Answer 2

Would you be willing to add a column to one of the tables, populate the new column with values from the other column, say through an update statement, then drop old column?

T1(c1 V_TYPE_1); T2(c1 V_TYPE_2);

(1) Add column to T2: T2(c1 V_TYPE_2, c2 V_TYPE_1);

(2) Update T2 so c1 and c2 are the same values for every row.

(3) Drop old column from T2: T2(c2 V_TYPE_1);

This solution would be a little easier if the table was not active. If the table was active you will want to add a trigger so that the two columns are in sync.

Answer 3

CAST it is, I agree with Sayan. Though, as there are two users involved, there are some in-between steps required - grant execute on type being most important, I'd say. Here's an example.

My users are scott and mike. Each of them has the same table description. scott is supposed to insert rows into mike's table.

Connected as scott:

SQL> show user
USER is "SCOTT"
SQL> create or replace type v_type as varray(5000) of number(1);
  2  /

Type created.

SQL> create table test (id number, a v_type);

Table created.

SQL> insert into test(id, a) values (1, v_type(1));

1 row created.

SQL>

Connected as mike: uses the same type as scott:

SQL> show user
USER is "MIKE"
SQL> create or replace type v_type as varray(5000) of number(1);
  2  /

Type created.

SQL> create table test (id number, a v_type);

Table created.

SQL> grant insert on test to scott;

Grant succeeded.

SQL>

Connected as scott, trying to insert row(s) into mike's table:

SQL> show user
USER is "SCOTT"
SQL> insert into mike.test (id, a) select id, a from test;
insert into mike.test (id, a) select id, a from test
                                         *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected MIKE.V_TYPE got SCOTT.V_TYPE

Let's try CAST:

SQL> insert into mike.test (id, a) select id, cast(a as mike.v_type) from test;
insert into mike.test (id, a) select id, cast(a as mike.v_type) from test
                                                   *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL>

In order to make it work, mike has to grant execute on their type to scott:

SQL> show user
USER is "MIKE"
SQL> grant execute on v_type to scott;

Grant succeeded.

SQL>

Finally, it works:

SQL> show user
USER is "SCOTT"
SQL> insert into mike.test (id, a) select id, cast(a as mike.v_type) from test;

1 row created.

SQL>