How to insert multiple rows in a variable PL/SQL

Question

I am trying to collect the member_id and how many different books that guy rented. Then i want this data put inside a variable so i can use it after . i am trying to do it like this

DECLARE

  nr_imprumuturi RECORD%ROWTYPE;
  nr_total_titluri Number(4);
  procent Number(3);
BEGIN

  select count(*) into nr_total_titluri
  from title;

  select count(distinct r.title_id),r.member_id bulk collect into nr_imprumuturi
  from member m, rental r
  group by r.member_id;

  select nr_imprumuturi.Nr_impr/nr_total_titluri *100 into procent
  from dual;






END;
/

i want the data to be put in nr_imprumuturi but i get this error :

Error report:
ORA-06550: line 11, column 67:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
ORA-06550: line 12, column 3:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 11, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The table record looks like this :

create table record(
 nr_impr Number(3),
 member_id Number(3),
 procent Number(3)
)

So you want a PL/SQL collection? Why aren't you just doing a simple count - why do you need PL/SQL at all? Are you trying to populate record? And did you mean to cross-join the two tables? — Alex Poole
– Alex Poole, Commented Oct 24, 2014 at 15:04
to be honest i am not sure what i want. I want a variable like a table with multiple rows so i can use it in an Update Statement like a normal table — Lucian Tarna
– Lucian Tarna, Commented Oct 24, 2014 at 15:06
for all the guys in the table. I also tried with a record and then from it i made another type of data with as table and then tried to use it on nr_imprumuturi but same result — Lucian Tarna
– Lucian Tarna, Commented Oct 24, 2014 at 15:07

Dmitriy · Accepted Answer · 2014-10-24 22:44:21Z

2

Probably you need nested table. Here is little example:

set serveroutput on;

create table test (id number, str varchar2(100));

insert into test values(1, 'a');
insert into test values(2, 'b');
insert into test values(3, 'c');
insert into test values(4, 'd');

declare
  type test_table is table of test%rowtype index by binary_integer;
  a_table test_table;
begin
  select *
  bulk collect into a_table
  from test
  where id > 1;

  dbms_output.put_line('Collection size is ' || a_table.count()); 
end;
/

drop table test;

This type you can use in different SQL statements, for details see documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99981

answered Oct 24, 2014 at 22:44

Dmitriy

5,58512 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lucian Tarna Over a year ago

you are right, this is what i needed ! thank you very much for taking the time to help me!

Collectives™ on Stack Overflow

How to insert multiple rows in a variable PL/SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related