克服通过数据库链接进行批量插入的限制 [英] Overcoming the restriction on bulk inserts over a database link

问题描述

当在数据库链接上使用时，似乎存在禁止在Oracle上使用forall .. insert的实现限制.这是一个简单的示例来演示:

It appears as though there's an implementation restriction that forbids the use of forall .. insert on Oracle, when used over a database link. This is a simple example to demonstrate:

connect schema/password@db1

create table tmp_ben_test (
   a number
 , b number
 , c date
 , constraint pk_tmp_ben_test primary key (a, b)
    );

Table created.

connect schema/password@db2
Connected.

declare

   type r_test is record ( a number, b number, c date);
   type t__test is table of r_test index by binary_integer;
   t_test t__test;

   cursor c_test is
    select 1, level, sysdate
      from dual
   connect by level <= 10
           ;

begin

   open c_test;
   fetch c_test bulk collect into t_test;

   forall i in t_test.first .. t_test.last
     insert into tmp_ben_test@db1
     values t_test(i)
            ;

   close c_test;

end;
/

非常令人困惑的是，它在9i中失败，并出现以下错误:

Very confusingly this fails in 9i with the following error:

在第1行出现错误:ORA-01400:无法将NULL插入 ("SCHEMA"."TMP_BEN_TEST"."A")ORA-02063:DB1的前一行 ORA-06512:在第18行

ERROR at line 1: ORA-01400: cannot insert NULL into ("SCHEMA"."TMP_BEN_TEST"."A") ORA-02063: preceding line from DB1 ORA-06512: at line 18

如果只是在签入11g后才意识到这是实施限制.

If was only after checking in 11g that I realised this was an implementation restriction.

第18行出现错误:ORA-06550:第18行第4列:PLS-00739:全部 远程表不支持INSERT/UPDATE/DELETE

ERROR at line 18: ORA-06550: line 18, column 4: PLS-00739: FORALL INSERT/UPDATE/DELETE not supported on remote tables

解决这个问题的最明显方法是将forall ..更改为:

The really obvious way round this is to change forall .. to:

for i in t_test.first .. t_test.last loop
    insert into tmp_ben_test@db1
    values t_test(i);
end loop;

但是，如果可能的话，我宁愿将其保留为单个插入. 汤姆·凯特(Tom Kyte)建议使用全局临时表.将数据插入GTT，然后通过DB链接，对于已经属于用户定义类型的一组数据来说似乎是过大的杀伤力.

but, I'd rather keep it down to a single insert if at all possible. Tom Kyte suggests the use of a global temporary table. Inserting the data into a GTT and then over a DB link seems like massive overkill for a set of data that is already in a user-defined type.

极端地只是简化了这个示例.我们不可能做一个简单的insert into，也不可能在GTT上完成所有操作.代码的大部分必须以用户定义的类型完成.

Just to clarify this example is extremely simplistic compared to what is actually happening. There is no way we will be able to do a simple insert into and there is no way all the operations could be done on a GTT. Large parts of the code have to be done in user-defined type.

是否有另一种或更简单或更简单的DMLy可以解决此限制?

Is there another, simpler or less DMLy, way around this restriction?

推荐答案

您在远程数据库上面临哪些限制?如果可以在其中创建对象，则有一个解决方法:在远程数据库上创建集合类型，并执行一个将集合作为参数并执行FORALL语句的过程.

What restrictions do you face on the remote database? If you can create objects there you have a workaround: on the remote database create the collection type and a procedure which takes the collection as a parameter and executes the FORALL statement.

这篇关于克服通过数据库链接进行批量插入的限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！