Ошибка: "ORA-06504: PL/SQL: Возвратные типы переменных Result Set или запроса не совпадают" с BULK COLLECT

Question

Eсть такая таблица, в которой только пара записей (в будущем их будет больше):

CREATE TABLE MYTABLE (ID, VALUE) AS
    SELECT 8,   265.7 FROM DUAL UNION ALL
    SELECT 7,   559.6 FROM DUAL;

Создаю к ней соответствующие типы данных:

CREATE OR REPLACE TYPE HASHMAP_NUM_TYPE_OBJ AS OBJECT (
    ID                 NUMBER,
    VALUE              NUMBER(10,1))
/    
CREATE OR REPLACE TYPE HASHMAP_NUM_TYPE IS TABLE OF HASHMAP_NUM_TYPE_OBJ
/

И пытаюсь получить записи, используя BULK COLLECT:

DECLARE 
    stats_by_measure HASHMAP_NUM_TYPE;
    cursor_1 sys_refcursor;
BEGIN 
    OPEN cursor_1 FOR
        SELECT ID, VALUE
        FROM MYTABLE;

    FETCH cursor_1 BULK COLLECT INTO stats_by_measure;
    CLOSE cursor_1;
END;
/

Error report -
ORA-06504: PL/SQL: Возвратные типы переменных Result Set или запроса не совпадают
ORA-06512: на line 9

Почему получаю эту ошибку? Что я делаю не так?

_{Свободный перевод вопроса Oracle PL/SQL 6504 with BULK COLLECT от участника @serkelion}

Answer 1

Причина ошибки - PL/SQL может поместить записи результата запроса в переменную с типом ROWTTYPE, но он не может поместить этот же результат в переменную с объектным типом данных, так как необходим конструктор, он не будет вызван неявно:

declare 
    obj hashmap_num_type_obj;
    rec my_table%rowtype;
begin 
    select 1, 2 into rec from dual;
    --select 1, 2 into obj from dual; --ORA-00947: не хватает значений для данных
                         --так нельзя --^^^^
    select hashmap_num_type_obj (1, 2) into obj from dual;
end;
/
PL/SQL procedure successfully completed.

Простое и эффективное решение - преобразовать сет результата в коллекцию с CAST (MULTISET query), и вернуть её простым INTO collection variable. И для коллекци, и для её элементов, конструкторы будут вызваны неявно. Воспроизводимый пример:

rc refcursor
declare 
    rs hashmap_num_type;
begin 
    select cast (
        multiset (select * from my_table) as hashmap_num_type) into rs 
    from dual;
    open :rc for select * from table (rs);
end;
/
    THE_ID  THE_VALUE
---------- ----------
         8      265,7
         7      559,6

Answer 2

Решение с BULK COLLECT может оказаться более эффективным, если в результате запроса получится большой набор данных для коллекции. Решением в примере ниже будет явный вызов конструктора. С клаузой LIMIT можно обработать большой результат запроса по частям:

declare 
    cursor c is 
        select hashmap_num_type_obj (id, value) 
        from mytable; 
    rs hashmap_num_type;
begin 
    open c;
    loop fetch c bulk collect into rs limit 10000;
        exit when rs.count = 0;
        for i in 1..rs.count loop 
            dbms_output.put_line ('result('||i||') id='||rs(i).id||' value='||rs(i).value); 
        end loop;    
    end loop; 
end;
/ 
result(1) id=8 value=265,7
result(2) id=7 value=559,6

Если объектный тип данных не предпололается использовать в SQL контексте, то простым решением будет - создать коллекцию с элементами типа ROWSET самого курсора:

declare 
    cursor c is 
        select id, value 
        from mytable;
    type resultSet is table of c%rowtype;    
    rs resultSet;
begin 
    open c;
    loop fetch c bulk collect into rs limit 10000;
        exit when rs.count = 0;
        for i in 1..rs.count loop 
            dbms_output.put_line ('result('||i||') id='||rs(i).id||' value='||rs(i).value); 
        end loop;    
    end loop; 
end;
/
result(1) id=8 value=265,7
result(2) id=7 value=559,6

PS Обратите внимание, условие выхода из цикла не NOTFOUND как обычно принято, это частый источник ошибки - коллекция в этом случае может содержать последние выбранные элементы.

БЛОГ НА HUSL

Ошибка: "ORA-06504: PL/SQL: Возвратные типы переменных Result Set или запроса не совпадают" с BULK COLLECT

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