Skip to Main Content
  • Questions
  • Bulk collect into multiple collections

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Don.

Asked: January 26, 2017 - 4:09 pm UTC

Last updated: January 26, 2017 - 11:52 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

I need to populate two collection as output from a stored procedure. The only difference between the two is the filter used to select the data. Our current method requires two "select x bulk collect into y from z where filter" statements (as illustrated in the example code below). Is it possible to do this with a single select?

-- table of test data 
create table foo (test_nbr number(19,0), is_prime char(1));
insert into foo values (1, 'N'); 
insert into foo values (2, 'Y'); 
insert into foo values (3, 'Y'); 
insert into foo values (4, 'N'); 
insert into foo values (5, 'Y'); 
commit; 
/

declare
    type foo_type is record (x foo.test_nbr%type);
    type bar is table of foo_type;
    prime_not bar;
    prime_yes bar;

begin
    null; 
    select test_nbr 
        bulk collect into prime_not 
    from foo 
        where is_prime = 'N';

    select test_nbr 
        bulk collect into prime_yes 
    from foo 
        where is_prime = 'Y';
    
    for i in 1 .. prime_not.count 
    loop 
        DBMS_OUTPUT.put_line('Not Prime: ' || prime_not(i).x); 
    end loop i;
    
    for i in 1 .. prime_yes.count 
    loop 
        DBMS_OUTPUT.put_line('Is Prime: ' || prime_yes(i).x); 
    end loop i;
end;
/

drop table foo;
/


The output from the example is just for illustration. prime_not and prime_yes would be output variables from the stored procedure.

Thanx in advance.


with LiveSQL Test Case:

and Chris said...

Ok, here goes:

- Create a SQL nested table type
- Use a case expression to return the number if it is prime. Then create another doing the opposite
- Place these expressions cast(collect()) calls, returning the type you created above
- And you're done!

create table foo (test_nbr number(19,0), is_prime char(1));
insert into foo values (1, 'N'); 
insert into foo values (2, 'Y'); 
insert into foo values (3, 'Y'); 
insert into foo values (4, 'N'); 
insert into foo values (5, 'Y'); 
commit; 
/

create or replace type tp as table of integer;
/

declare
  prime tp;
  not_prime tp;
begin
  select cast(collect(case when is_prime = 'Y' then test_nbr end) as tp), 
         cast(collect(case when is_prime = 'N' then test_nbr end) as tp)
  into   prime, not_prime
  from foo;
  
  for i in prime.first .. prime.last loop
    dbms_output.put_line('PRIME: ' || prime(i));
  end loop;
  
  for i in not_prime.first .. not_prime.last loop
    dbms_output.put_line('NOT PRIME: ' || not_prime(i));
  end loop;
end;
/

PRIME: 2
PRIME: 3
PRIME: 5
NOT PRIME: 1
NOT PRIME: 4

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Don Simpson, January 26, 2017 - 5:07 pm UTC

That's perfect! We currently have SQL nested table types for the actual output collections I need, so this will drop right into place.

Thanx so much,
Don

Connor McDonald
January 26, 2017 - 11:52 pm UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library