Ошибка ORA-00947 "not enough values" при returning BULK COLLECT INTO во вложеную тблицу

Question

Получаю ошибку при попытке получить массив значений из открытого курсора при обновлении данных во вложенную таблицу с пользовательским типом данных.

Так создаю вложенную таблицу с пользовательским типом на уровне БД:

CREATE OR REPLACE TYPE "FOO_STATUS" AS OBJECT (
    a NUMBER, b NUMBER, c NUMBER)
/
CREATE OR REPLACE TYPE FOO_STATUS_TABLE IS TABLE OF FOO_STATUS
/

Так выглядит процедура в пакете:

PROCEDURE foo (...) IS
    l_foo FOO_STATUS_TABLE := FOO_STATUS_TABLE();
BEGIN
    UPDATE foo_table SET ...
    WHERE ...
    RETURNING foo_table_a, foo_table_b, c BULK COLLECT INTO l_foo;
END;

Но получаю ошибку:

PL/SQL: ORA-00947: not enough values

Где не хватает значений, и как исправить ошибку?

_{Свободный перевод вопроса ORA-00947 "not enough values" when UPDATE returning BULK COLLECT INTO user created TYPE TABLE от участника @Rodrigo Gurgel}

Answer 1

Так как l_foo это вложенная таблица с колонкой объектного типа, то необходимо собирать в неё сущности этого объектного типа, а не отдельные значения:

returning foo_status (a, b, c) bulk collect into l_foo

Кроме того, так будут возвращены новые значения колонок. Но часто интересно сохранить значения колонок до их изменения (стартые). Следуюший воспроизводимый пример сохранит оба значения:

var rc refcursor 
declare
    old footab;
    new footab;
begin
    update foo f1 set 
        a = a*10, b = b*10, c = c*10
    where 1=1
    returning (
        select foorow (a,b,c) 
        from dual), foorow (a,b,c) bulk collect into old, new;
        
    open :rc for 
        select 'old:' vals, o.* from table (old) o union all 
        select 'new:' vals, n.* from table (new) n;
end;
/

VALS          A          B          C
---- ---------- ---------- ----------
old:          1          2          3
old:          4          5          6
old:          7          8          9
new:         10         20         30
new:         40         50         60
new:         70         80         90

На заметку

Если UPDATE содержит клаузу log errors reject limit, а при изменении возникнет ошибка, то returning вернёт значения только для обновлённых записей. Новые значения для записей, на которых произошла ошибка, можно узнать в логе ошибок, например, в таблице err$_foo для примера выше.

Подготовка схемы для примера:

create or replace type foorow as object (a int, b int, c int);
/
create or replace type footab as table of foorow;
/
create table foo (a int, b int, c int)
/
insert all 
    into foo values (1,2,3)
    into foo values (4,5,6)
    into foo values (7,8,9)
select 1 from dual;
commit;

БЛОГ НА HUSL

Ошибка ORA-00947 "not enough values" при returning BULK COLLECT INTO во вложеную тблицу

Ответы (1 шт):