Массовая вставка в Oracle PL/SQL

У меня есть около 5 миллионов записей, которые нужно скопировать из таблицы одной схемы в таблицу другой схемы (в той же базе данных). Я подготовил сценарий, но он дает мне следующую ошибку.

ORA-06502: PL / SQL: ошибка числа или значения: массовая привязка: ошибка в определении

Ниже мой сценарий

DECLARE    
    TYPE tA IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;
    TYPE tB IS TABLE OF SchemaA.TableA.band%TYPE INDEX BY PLS_INTEGER;
    TYPE tD IS TABLE OF SchemaA.TableA.start_date%TYPE INDEX BY PLS_INTEGER;
    TYPE tE IS TABLE OF SchemaA.TableA.end_date%TYPE INDEX BY PLS_INTEGER;        
    rA tA;
    rB tB;
    rD tD;
    rE tE;
    f number :=0;    
BEGIN

    SELECT col1||col2||col3 as main_col, band, effective_start_date as start_date, effective_end_date as end_date 
    BULK COLLECT INTO rA, rB, rD, rE 
    FROM schemab.tableb;

    FORALL i IN rA.FIRST..rE.LAST
        insert into SchemaA.TableA(main_col, BAND, user_type, START_DATE, END_DATE, roll_no) 
        values(rA(i), rB(i), 'C', rD(i), rE(i), 71);

    f:=f+1;

    if (f=10000) then
        commit;
    end if;

end;

Не могли бы вы помочь мне найти ошибку?

Источник

4 ответа

Решение

Следующий скрипт работал для меня, и я смог загрузить около 5 миллионов данных в течение 15 минут.

 ALTER SESSION ENABLE PARALLEL DML
 /

 DECLARE


 cursor c_p1 is 
    SELECT col1||col2||col3 as main_col, band, effective_start_date as start_date, effective_end_date as end_date 
    FROM schemab.tableb;

    TYPE TY_P1_FULL is table of c_p1%rowtype
    index by pls_integer;

   v_P1_FULL TY_P1_FULL;

   v_seq_num number;

BEGIN

open c_p1;

loop

fetch c_p1 BULK COLLECT INTO v_P1_FULL LIMIT 10000;
exit when v_P1_FULL.count = 0;
FOR i IN 1..v_P1_FULL.COUNT loop


INSERT /*+ APPEND */ INTO schemaA.tableA VALUES (v_P1_FULL(i));

end loop;
commit;
end loop;
close c_P1;
dbms_output.put_line('Load completed');


end;

-- Disable parallel mode for this session
ALTER SESSION DISABLE PARALLEL DML
/
Источник

Почему не простой

insert into SchemaA.TableA (main_col, BAND, user_type, START_DATE, END_DATE, roll_no) 
SELECT col1||col2||col3 as main_col, band, 'C', effective_start_date, effective_end_date, 71 
FROM schemab.tableb;

это

f:=f+1;
if (f=10000) then
   commit;
end if;

не имеет никакого смысла. f становится 1 - вот и все. f=10000 никогда не будет правдой, поэтому вы не делаете коммит.

Источник

ORA-06502: PL/SQL: ошибка числа или значения: массовая привязка: ошибка в определении

Вы получаете эту ошибку, потому что у вас есть литерал в VALUES пункт о INSERT, FORALL ожидает, что все будет связано с массивом.

У вашей программы буквально огромная проблема. У вас нет LIMIT на BULK COLLECT пункт, так что это будет пытаться загрузить все пять миллионов записей из TableB в ваши коллекции. Это унесет предел памяти вашей сессии.

Точка использования BULK COLLECT а также FORALL состоит в том, чтобы откусить куски большего набора данных и обрабатывать их партиями. Для этого вам нужна петля. Цикл не имеет условия FOR: вместо этого проверьте, вернула ли выборка что-либо, и завершите работу, когда массив имеет нулевые записи.

DECLARE    
    TYPE recA IS RECORD (
        main_col SchemaA.TableA.main_col%TYPE
        , band SchemaA.TableA.band%TYPE
        , start_date date
        , end_date date
        , roll_ni number);
    TYPE recsA is table of recA
    nt_a recsA;
    f number :=0;    
    CURSOR cur_b is
        SELECT col1||col2||col3 as main_col, 
               band, 
               effective_start_date as start_date, 
               effective_end_date as end_date ,
               71 as roll_no
    FROM schemab.tableb;
BEGIN
    open cur_b;
    loop
        fetch curb_b bulk collect into nt_a limit 1000;
        exit when nt_a.count() = 0;

        FORALL i IN rA.FIRST..rE.LAST
            insert into SchemaA.TableA(main_col, BAND, user_type, START_DATE, END_DATE, roll_no) 
            values nt_a(i);

        f := f + sql%rowcount;       
        if (f > = 10000) then
            commit;
            f := 0;
        end if;
    end loop;
    commit;
    close cur_b;
end;

Обратите внимание, что выдача коммитов внутри цикла противопоказана. Вы открываете себя для ошибок времени выполнения, таких как ORA-01002 и ORA-01555. Если ваша программа потерпит неудачу на полпути, вам будет очень трудно возобновить ее без проблем. Конечно, сохранитесь, если у вас есть проблемы с табличным пространством UNDO, но правильный ответ - заставить администратора баз данных увеличить табличное пространство UNDO, а не ослабить ваш код.

"Я использую массовую вставку, потому что она дает лучшую производительность"

Правда, что BULK COLLECT а также FORALL ... INSERT является более продуктивным, чем CURSOR FOR петля с построчными одиночными вставками. Это не более эффективно, чем чистый SQL INSERT INTO ... SELECT, Ценность конструкции в том, что она позволяет нам манипулировать содержимым массива перед его вставкой. Это обрабатывается, если у нас есть сложные бизнес-правила, которые могут применяться только программно.

Источник

Пожалуйста, попробуйте после изменения первых 2 строк вашего кода ниже:

DECLARE

TYPE tA IS TABLE OF SchemaA.TableA.main_col%TYPE INDEX BY PLS_INTEGER;
...
...

Это может быть из-за несоответствия типа / длины данных. В разделе объявлений вы пропустили объявление одного объекта для наследования типа из таблицы.

Также, как уже упоминалось, логика коммита не сделает магию для вас. Лучше использовать LIMIT с BULL COLLECT

Источник
Другие вопросы по тегам