Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bulk collect into record type

John O'TooleJun 2 2010 — edited Jun 2 2010
Sorry for the dumb question - I'm doing something really simple wrong here, but can't figure it out. I want to select a few rows of a table into a cursor, then bulk collect it into a record. I'll eventually extend the record to include some extra fields which I'll select back from functions, but I can't get this simple test case to run...

PLS-00497 is the main error.

Thanks in advance.
create table test (
id number primary key,
val varchar2(20),
something_else varchar2(20));

insert into test (id, val,something_else) values (1,'test1','else');
insert into test (id, val,something_else) values (2,'test2','else');
insert into test (id, val,something_else) values (3,'test3','else');
insert into test (id, val,something_else) values (4,'test4','else');

commit;

SQL> declare
  2   cursor test_cur is
  3   (select id, val
  4   from test);
  5
  6   type test_rt is record (
  7     id   test.id%type,
  8     val      test.val%type);
  9
 10   test_rec test_rt;
 11
 12  begin
 13    open test_cur;
 14    loop
 15      fetch test_cur bulk collect into test_rec limit 10;
 16       null;
 17     exit when test_rec.count = 0;
 18    end loop;
 19    close test_cur;
 20  end;
 21  /
    fetch test_cur bulk collect into test_rec limit 10;
                                     *
ERROR at line 15:
ORA-06550: line 15, column 38:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
ORA-06550: line 17, column 21:
PLS-00302: component 'COUNT' must be declared
ORA-06550: line 17, column 2:
PL/SQL: Statement ignored
This post has been answered by Tubby on Jun 2 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 30 2010
Added on Jun 2 2010
3 comments
21,086 views