对于ORACLE参与的异构数据库的分布式事务,ORACLE允许 INSERT INTO 本地表 SELECT * FROM 远程,
但是不允许INSERT INTO 远程表 SELECT * FROM 本地表:
否则就会引发:
但是不允许INSERT INTO 远程表 SELECT * FROM 本地表:
否则就会引发:
ORA-02025: all tables in the SQL statement must be at the remote database.
如下一个简单的例子。
(远程库是一个DB2V9.5的数据库。目标数据库是ORACLE 10.2.0.5的数据库。)
首先在远程DB2中创建测试表T。
点击(此处)折叠或打开
- db2 => create table t(id int)
- DB20000I SQL命令成功完成。
- db2 => insert into t values(1)
- DB20000I SQL命令成功完成。
- db2 => select * from t
-
- ID
- -----------
-
- 1
-
- 1 条记录已选择。
-
- db2 => commit
- DB20000I SQL命令成功完成。
- db2 =>
我已经配置好了透明网关,并且在ORACLE中建立了DBLINK DB2DB指向远程数据库。
数据也能从正常查询到。
点击(此处)折叠或打开
- SQL> SELECT * FROM T@DB2DB
- 2 ;
-
- ID
- ----------
-
- 1
也可以将DB2中的数据插入到ORACLE中,如下:
点击(此处)折叠或打开
- SQL> CREATE TABLE ORACLE_T (ID INT);
-
- 表已创建。
-
- SQL> INSERT INTO ORACLE_T SELECT * FROM T@DB2DB;
-
- 已创建 1 行。
-
- SQL> COMMIT;
-
- 提交完成。
-
- SQL> SELECT * FROM ORACLE_T;
-
- ID
- ----------
-
- 1
-
- SQL>
但是不允许如下的形式将ORACLE的数据插入到DB2中:
点击(此处)折叠或打开
- SQL> INSERT INTO T@DB2DB SELECT * FROM ORACLE_T;
- INSERT INTO T@DB2DB SELECT * FROM ORACLE_T
- *
- 第 1 行出现错误:
- ORA-02025: all tables in the SQL statement must be at the remote database
如下的方法是可以的:
点击(此处)折叠或打开
- SQL> INSERT INTO T@DB2DB VALUES(2);
-
- 已创建 1 行。
-
- SQL> COMMIT;
-
- 提交完成。
-
- SQL> SELECT * FROM T@DB2DB;
-
- ID
- ----------
-
- 1
- 2
-
- db2端:
-
- db2 => select * from t
-
- ID
- -----------
-
- 1
- 2
-
- 2 条记录已选择。
-
- db2 =>
因此可以采用游标循环的方法来解决这个问题:
点击(此处)折叠或打开
- SQL> SELECT * FROM ORACLE_T;
-
- ID
- ----------
-
- 1
-
- SQL> DELETE FROM ORACLE_T;
-
- 已删除 1 行。
-
- SQL> DELETE FROM T@DB2DB;
-
- 已删除2行。
-
- SQL> COMMIT;
-
- 提交完成。
-
- SQL> INSERT INTO ORACLE_T SELECT ROWNUM FROM DUAL
- 2 CONNECT BY LEVEL < =5;
-
- 已创建5行。
-
- SQL> COMMIT;
-
- 提交完成。
-
- SQL> SELECT * FROM ORACLE_T;
-
- ID
- ----------
-
- 1
- 2
- 3
- 4
- 5
-
- SQL> SELECT * FROM T@DB2DB;
-
- 未选定行
-
-
-
-
- SQL> BEGIN
- 2 FOR X IN (SELECT ID FROM ORACLE_T) LOOP
- 3 INSERT INTO T@DB2DB VALUES(X.ID);
- 4 END LOOP;
- 5 COMMIT;
- 6 END;
- 7 /
-
- PL/SQL 过程已成功完成。
-
- SQL> SELECT * FROM T@DB2DB;
-
- ID
- ----------
-
- 1
- 2
- 3
- 4
- 5
注意:远程表不支持FORALL的批量插入。
点击(此处)折叠或打开
- SQL> delete from t@db2db;
-
- 已删除5行。
-
- SQL> commit;
-
- 提交完成。
-
- SQL> declare
- 2 cursor mycursor is select id from oracle_t;
- 3 type id_table_type is table of number index by binary_integer;
- 4 id_table id_table_type;
- 5 i int;
- 6 begin
- 7 open mycursor;
- 8 fetch mycursor bulk collect into id_table ;
- 9 close mycursor;
- 10 forall i in id_table.first..id_table.last
- 11 insert into t@db2db values(id_table(i));
- 12 commit;
- 13 end;
- 14 /
- forall i in id_table.first..id_table.last
- *
- 第 10 行出现错误:
- ORA-06550: line 10, column 3:
- PLS-00739: FORALL INSERT/UPDATE/DELETE not supported on remote tables
除此之外,SQLPLUS的COPY命令也可以解决这个问题。
点击(此处)折叠或打开
- SQL> COPY FROM report/report@reportdb insert t@db2db using select
- id from oracle_t
-
- 数组提取/绑定大小为 15。(数组大小为 15)
- 将在完成时提交。(提交的副本为 0)
- 最大 long 大小为 80。(long 为 80)
- 5 行选自 report@reportdb。
- 5 行已插入 T@DB2DB。
- 5 行已提交至 T@DB2DB (位于 DEFAULT HOST 连接)。
-
- SQL> select * from t@db2db;
-
- ID
- ----------
-
- 1
- 2
- 3
- 4
- 5