This discussion is archived
6 Replies Latest reply: Nov 27, 2013 1:30 PM by TPD-Opitz-Consulting-com RSS

adding all entries of a collection to another?

TPD-Opitz-Consulting-com Expert
Currently Being Moderated

hello,

 

my database is a 11g

 

I have a function (in a package) that returnes a collection.

I want to call the function multipe times and the caller should combine all selected entries in a (new?) single collection :

create or replace package my_test is
  procedure public_proc;
end my_test;
create or replace package body my_test is
cursor test_data(selected_type varchar2) is
  select (1+ABS(MOD(dbms_random.random,100000))) as id, selected_type||level as my_value from dual connect by level<5;
type my_type is table of test_data%row_type;
function private_function(selected_type varchar2) return  my_type is
  v_my_type my_type := my_type();
begin
  open  test_data(selected_type=> selected_type);
  fetch  test_data bulk collect into  v_my_type;
  close  test_data;
  return  v_my_type;
end  private_function;
procedure public_proc is
  v_my_type_a my_type := my_type();
  v_my_type_b my_type := my_type();
  v_my_type_ab my_type := my_type();
begin
  v_my_type_a := private_function(selected_type=>'A');
  v_my_type_b := private_function(selected_type=>'B');
  -- simplest solution to combine  v_my_type_a and  v_my_type_b to  v_my_type_ab, possible without explicit loop?
for i in  v_my_type_ab.first..v_my_type_ab.last loop
    dbms_output.put_line('id: '||v_my_type_ab(i).id||', value:'||v_my_type_ab(i).my_value);
end loop;
end public_proc;
end my_test;

desired output (x instead of random number):

id:x, value:A1
id:x, value:A2
id:x, value:A3
id:x, value:A4
id:x, value:B1
id:x, value:B2
id:x, value:B3
id:x, value:B4

bye

TPD

  • 1. Re: adding all entries of a collection to another?
    gaverill Journeyer
    Currently Being Moderated

    Not possible with PL/SQL collection types, but if they are SQL table types (i.e. CREATE TYPE ... IS TABLE OF ...), then you can use the MULTISET UNION ALL operator, both in SQL and PL/SQL, to combine the two collections. In your case, where you have a table of records, not just scalars, then you would have to create an SQL OBJECT type whose attributes correspond to the fields of your PL/SQL record type, and your collections would contain object instances rather than records.

     

    Gerard

  • 2. Re: adding all entries of a collection to another?
    rp0428 Guru
    Currently Being Moderated
    Not possible with PL/SQL collection types

    Sure it is - the collection just needs to be a NESTED TABLE

    DECLARE
       TYPE nested_type IS TABLE OF VARCHAR2(30);
       v1 nested_type;
       v2 nested_type;
       v3 nested_type;
    BEGIN
       v1 := nested_type('Shipping','Sales');
       v2 := nested_type('Finance','Payroll');
       v3 := v1 MULTISET UNION v2;
       for i in v3.first..v3.last loop
         dbms_output.put_line(v3(i));
       end loop;
    END;
    /

     

    Shipping

     

    Sales

     

    Finance

     

    Payroll

    See the 'Multiset Operations for Nested Tables' section in the object-relational dev guide

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#i471415

    Multiset Operations for Nested Tables

    This section describes multiset operators for nested tables. Multiset operations are not available for varrays.

    The SQL examples in this section use the nested tables created in Example 5-5 and the objects created in Example 5-1.

  • 3. Re: adding all entries of a collection to another?
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated

    can I also do a bulk collect into against a nested type collection?

     

    bye

    TPD

  • 4. Re: adding all entries of a collection to another?
    rp0428 Guru
    Currently Being Moderated

    can I also do a bulk collect into against a nested type collection?

    Ok - is that a trick question?

     

    You are asking if you can do what the code you first posted already does?

     

    Hmmm - well I'm gonna say YES - since your code is already doing it.

     

    What type of collection, exactly, do you think you defined with this line?

    type my_type is table of test_data%row_type;

    Of course 'row_type' is invalid; it is %ROWTYPE.

     

    If you don't know what type of collection that is then I suggest you review 'Using PL/SQL Collections and Records' in the PL/SQL Language doc

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm

     

    It explains each of the collection types, discusses how to choose the appropriate one and has examples of using them.

    DECLARE

       cursor emp_data (my_job emp.job%type) is select *

          from emp where job = my_job; 

       type my_emp_data is table of emp_data%rowtype; 

       v1 my_emp_data;

       v2 my_emp_data;

       v3 my_emp_data;

    BEGIN

       open emp_data ('CLERK');

       fetch emp_data bulk collect into  v1;

       close emp_data; 

       open emp_data ('SALESMAN');

       fetch emp_data bulk collect into  v2;

       close emp_data; 

       v3 := v1 MULTISET UNION v2;

       for i in v3.first..v3.last loop

         dbms_output.put_line(v3(i).ename || ' - ' || v3(i).job);

       end loop;

    END;

    /

     

    SMITH - CLERK

     

    ADAMS - CLERK

     

    JAMES - CLERK

     

    MILLER - CLERK

     

    ALLEN - SALESMAN

     

    WARD - SALESMAN

     

    MARTIN - SALESMAN

     

    TURNER - SALESMAN

    Note also that I am NOT using a query to combine the collections:

    v3 := v1 MULTISET UNION v2;

  • 5. Re: adding all entries of a collection to another?
    gaverill Journeyer
    Currently Being Moderated

    D'oh, I thought I had tried that... fail I guess!

     

    Gerard

  • 6. Re: adding all entries of a collection to another?
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated

    rp0428 wrote:

     

    can I also do a bulk collect into against a nested type collection?

    Ok - is that a trick question?

     

    You are asking if you can do what the code you first posted already does?

    Yes, because your first answer made me feel I have to change my code in this respect...

    After this answer of yours I understand that I have to replace the comment in my question with this:

    v_my_type_ab := v_my_type_a MULTISET UNION v_my_type_b;

     

    bye

    TPD

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points