sqloracleplsqloracle11gbulk-collect

How to fetch the Rowid with all the columns of a table using bulk collect for a cursor in oracle


declare
    cursor c_1 is select a.*, a.rowid from an_test a;

    type t_1 is table of an_test%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;
        ...
    end loop;
end;

I am trying to execute the above code but it is giving me the error like :

PLS-00597: expression 'tab' in the INTO list is of wrong type.

Is there any other/alternate way to do like this?

Thanks in advance.


Solution

  • If you can manage with the rowid in the same record, base your type on the cursor instead of the table:

    declare
        cursor c is
            select a.*, a.rowid
            from   customer a;
    
        type t is table of c%rowtype;
        tab t;
    
    begin
        open c;
        fetch c bulk collect into tab limit 1000;
    end;