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

Question

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.

score 2 · Accepted Answer · answered Aug 08 '20 at 08:16

2

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;

answered Aug 08 '20 at 08:16

William Robertson

15,273
4
38
44

Yeah it helped me, Thanks a lot. – Node98 Aug 08 '20 at 08:55

score 0 · Answer 2 · answered Aug 07 '20 at 20:34

Should be something like this:

SQL> set serveroutput on;
SQL> declare
  2    cursor c1 is select d.*, d.rowid from dept d;
  3    type t1r is record
  4      (deptno   number,
  5       dname    varchar2(20),
  6       loc      varchar2(20),
  7       rowid    urowid
  8      );
  9    type t1t is table of t1r;
 10    t1   t1t;
 11  begin
 12    open c1;
 13    fetch c1 bulk collect into t1;
 14    close c1;
 15
 16    for i in t1.first .. t1.last loop
 17      dbms_output.put_line(rpad(t1(i).dname, 15, ' ') ||' '|| t1(i).rowid);
 18    end loop;
 19  end;
 20  /
ACCOUNTING      AAAGvqAAEAAAAIUAAA
RESEARCH        AAAGvqAAEAAAAIUAAB
SALES           AAAGvqAAEAAAAIUAAC
OPERATIONS      AAAGvqAAEAAAAIUAAD

PL/SQL procedure successfully completed.

SQL>

score 0 · Answer 3 · answered Aug 07 '20 at 22:30

0

You don't need extra variable for rowid in this case. Just remove , row from fetch c_1 bulk collect into tab, row limit 1000;

It should be simple fetch c_1 bulk collect into tab limit 1000;

answered Aug 07 '20 at 22:30

Sayan Malakshinov

8,492
1
19
27

but i need rowid from the table for deletion purpose. – Node98 Aug 08 '20 at 08:03

score 0 · Answer 4 · edited Aug 09 '20 at 03:09

0

SQL> set serveroutput on;
SQL> declare
       cursor c1 is select d.*, d.rowid from dept d;
       type t1r is record
         (deptno   number,

              dname    varchar2(20),

          loc      varchar2(20),
          rowid    urowid
         );
       type t1t is table of t1r;
       t1   t1t;
     begin
       open c1;
       fetch c1 bulk collect into t1;
       close c1;
   
       for i in t1.first .. t1.last loop
         dbms_output.put_line(rpad(t1(i).dname, 15, ' ') ||' '|| t1(i).rowid);
       end loop;
   end;
   /

edited Aug 09 '20 at 03:09

F. Müller

3,969
8
38
49

answered Aug 08 '20 at 14:39

mayureshrpatil

1

Please don't add the row numbers. – F. Müller Aug 08 '20 at 20:37

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

4 Answers4