Как выбирать ROWID со всеми столбцами таблицы в BULK COLLECT для явного курсора?

Question

Минимальный пример того, что пытаюсь сделать:

create table tab1 (id int, memo varchar2 (16), dt date);
insert into tab1
    select level, 'memo '||level, trunc (sysdate-9)+level
    from dual connect by level<=9; 

declare
    cursor c_1 is select a.*, a.rowid from tab1 a;
    type t_1 is table of tab1%rowtype;
    type l_row_id is table of UROWID;
    tab t_1; 
    row l_row_id;
begin
    open c_1;
    loop
        fetch c_1 bulk collect into tab, row limit 1000;
        exit when tab.count = 0;  
    end loop;
end;
/

При попытке запустить этот код, получаю ошибку:

Error report -
ORA-06550: line 10, column 37:
PLS-00597: expression 'TAB' in the INTO list is of wrong type

Есть ли какой-то другой путь решения?

_{Свободный перевод вопроса How to fetch the Rowid with all the columns of a table using bulk collect for a cursor in oracle от участника @krishh}

Answer 1

Причина ошибки не совсем ясна. В офф. документации нет явного ограничения на использование нескольких коллекций в BULK COLLECT INTO. Но если попробовать тоже самое с неявным курсором, то сообщение об ошибке более понятно:

declare
    type rec is record (id int, memo varchar2 (16));
    type ntrec is table of rec;
    type ntint is table of int;
    t ntrec; 
    r ntint;
begin
    select t.id, t.memo, 1 bulk collect into t, r from tab1 t;
end;
/

Error report -
ORA-06550: line 8, column 5:
PLS-00494: coercion into multiple record targets not supported

Принудительное приведение к нескольким целям с типом RECORD (композитного типа) не поддерживается.

То есть, в данном случае решением будет, привести к одной коллекции с типом элементов курсор%ROWTYPE.

Вот простейший пример, как надо сделать:

var rc refcursor
declare
    cursor cur is select t.rowid, t.* from tab1 t;
    type ntrec is table of cur%rowtype; 
    t ntrec; 
begin
    open cur;
    fetch cur bulk collect into t;
    t.delete (1,2); /** здесь какая-то бизнес логика */ 
    forall ix in indices of t
        update tab1 set
            memo = memo||'**', dt=sysdate
        where rowid = t(ix).rowid; 

    open :rc for select * from tab1;     
end;
/

Результат:

        ID MEMO             DT        
---------- ---------------- ----------
         1 memo 1           2020-08-15
         2 memo 2           2020-08-16
         3 memo 3**         2020-08-23
[...]

БЛОГ НА HUSL

Как выбирать ROWID со всеми столбцами таблицы в BULK COLLECT для явного курсора?

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