从varray更改列数据类型

Question

在Oracle数据库中，除了两个表中的一个列是数字数组外，还有两个相互镜像的表的两个模式，但在模式A中是A.VARRAY，在模式B中是B.VARRAY。

因此，我无法在表之间迁移数据，因为它们具有不一致的数据类型。

有没有办法在不丢失数据的情况下将列数据类型从A.varray更改为B.varray？

Answer 1

只需使用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

CAST是，我同意萨扬的观点。虽然，由于涉及到两个用户，但我认为最重要的是需要执行一些中间步骤--在类型上执行。下面是一个例子。

我的用户是scott和mike。每个表都有相同的表描述。scott应该将行插入到mike的表中。

连接为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>

连接为mike：使用与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>

连接为scott，试图将行插入到mike的表中：

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

让我们试试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>

为了使其工作，mike必须将其类型的执行授予scott。

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

Grant succeeded.

SQL>

最后，它起作用了

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>

Answer 3

您是否愿意向其中一个表中添加一列，用来自另一列的值填充新列，例如通过update语句，然后删除旧列？

T1(c1 V_TYPE_1)；T2(c1 V_TYPE_2)；

(1) T2: T2(c1 V_TYPE_2，c2 V_TYPE_1)；

(2)更新T2，以便c1和c2对每一行都是相同的值。

(3)从T2中删除旧柱: T2(c2 V_TYPE_1)；

如果表没有活动，这个解决方案就会容易一些。如果表处于活动状态，则需要添加触发器，以便两列保持同步。