Wygląda na to, że istnieje ograniczenie implementacyjne, które zabrania używania forall .. insert w Oracle, gdy jest używane przez łącze do bazy danych. Oto prosty przykład do zademonstrowania:

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

Bardzo mylące to niepowodzenie w 9i z następującym błędem:

BŁĄD w wierszu 1: ORA-01400: nie można wstawić wartości NULL w („SCHEMA”.„TMP_BEN_TEST”.„A”) ORA-02063: poprzedni wiersz z DB1 ​​ORA-06512: w wierszu 18

Jeśli dopiero po sprawdzeniu 11g zdałem sobie sprawę, że jest to ograniczenie implementacyjne.

BŁĄD w wierszu 18: ORA-06550: wiersz 18, kolumna 4: PLS-00739: FORALL INSERT/UPDATE/DELETE nie jest obsługiwane na zdalnych tabelach

Naprawdę oczywistym sposobem na obejście tego jest zmiana forall .. na:

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

Ale wolę ograniczyć to do jednej wstawki, jeśli to w ogóle możliwe. Tom Kyte sugeruje użycie globalnej tabeli tymczasowej. Wstawianie danych do GTT, a następnie przez łącze DB wydaje się ogromną przesadą dla zestawu danych, który jest już w typie zdefiniowanym przez użytkownika.

Samo wyjaśnienie tego przykładu jest niezwykle uproszczone w porównaniu z tym, co się faktycznie dzieje. Nie ma mowy, abyśmy byli w stanie wykonać proste insert into i nie ma mowy, aby wszystkie operacje mogły zostać wykonane na GTT. Duże części kodu muszą być wykonane w typie zdefiniowanym przez użytkownika.

Czy istnieje inne, prostsze lub mniej DMLy, obejście tego ograniczenia?

2
Ben 10 maj 2012, 16:50

2 odpowiedzi

Najlepsza odpowiedź

Jakie ograniczenia napotykasz na zdalnej bazie danych? Jeśli możesz tworzyć tam obiekty, możesz obejść ten problem: na zdalnej bazie danych utwórz typ kolekcji i procedurę, która przyjmuje kolekcję jako parametr i wykonuje instrukcję FORALL.

2
APC 10 maj 2012, 17:06

Jeśli utworzysz typ t__test/r_test w db2, a następnie utworzysz dla nich publiczny synonim w db1, powinieneś być w stanie wywołać procedurę z db1 do db2, wypełniając tabelę t_table i wracając do db1. Wtedy powinieneś być w stanie wstawić do swojej lokalnej tabeli.

Zakładam, że użyjesz spakowanych typów i procedur w prawdziwym świecie, a nie anonimowych bloków.

Nie byłoby to również idealne rozwiązanie dla dużych zbiorów danych, wtedy GTT lub podobne byłyby lepsze.

1
Joli 15 czerwiec 2012, 15:40