2

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

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

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

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

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

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;

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.

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.

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

Ben
  • 51,770
  • 36
  • 127
  • 149
  • have you tried the GTT ideea? Compare the performance in 11g. I guess is not an overkill, but a speedy way to move dates. – Florin Ghita May 10 '12 at 14:31
  • No I haven't tried it. I will obviously if there's no other option. It just seems strange to take something from memory; move it into a different memory then do some DML. I was hoping for a more simplistic solution. If one exists. – Ben May 10 '12 at 21:27
  • Think a little of it. The GTT part is pure SQL, and will be fast(with dblink penalty). What is slow is the ps/sql part with arrays... – Florin Ghita May 11 '12 at 06:00

2 Answers2

2

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.

APC
  • 144,005
  • 19
  • 170
  • 281
  • I have no restrictions anywhere; these are just processing DBs and not connected to the outside world. It took me a few seconds to see what you mean; execute a procedure on `db1` from db2` that takes a type as a parameter and does the insert for me? – Ben May 10 '12 at 16:49
1

If you create the t__test/r_test type in db2 and then create a public synonym for them on db1 then you should be able to call a procedure from db1 to db2 filling the t_table and returning in to db1. Then you should be able to insert into your local table.

I'm assuming you would use packaged types and procedures in the real world, not anonymous blocks.

Also, it would not be the ideal solution for big datasets, then GTT or similar would be better.

Joli
  • 11
  • 1