How to merge two INDEX BY tables?

CGMS

Hi All,

Is there any way to merge the two INDEX BY tables?

i tried MULTISET UNION DISTINCT from the below link:
http://www.oracle.com/technology/oramag/oracle/03-sep/o53plsql.html

I got the PLS:00306: wrong number or types of arguments in call to 'MULTISET_UNION_DISTINCT' error.

Code:

declare

TYPE rec_nbrchk IS TABLE OF test.rec_nbr%TYPE
INDEX BY BINARY_INTEGER;

rec_nbr_chk rec_nbrchk;
mlcl_scope_chk rec_nbrchk;
all_chk rec_nbrchk;

begin

all_chk := (rec_nbr_chk )
MULTISET UNION DISTINCT
(scope_chk);
end;

Thanks in advance.

Suresh.

Karthick2003

You cant use MULTISET with INDEX BY tables. You must do it with PL/SQL Nested Table Type.

declare
 TYPE rec_nbrchk IS TABLE OF emp.empno%TYPE;

 rec_nbr_chk rec_nbrchk;
 mlcl_scope_chk rec_nbrchk; 
 all_chk rec_nbrchk;
begin
 all_chk := (rec_nbr_chk ) MULTISET UNION DISTINCT (mlcl_scope_chk);
end;

This mentioned in the first line of the document. Please read the content of the below link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/operators006.htm

Twinkle

Hi,

Check the below link:

Under the header :Type Demos

http://www.psoug.org/reference/type.html

Something like this??

SQL> Declare
  2  TYPE type_rec_emp is table of emp.empno%type;
  3  rec_empno1 type_rec_emp;
  4  rec_empno2 type_rec_emp;
  5  rec_empno3 type_rec_emp;
  6  Begin
  7  rec_empno3 := 
  8   rec_empno1
  9   MULTISET UNION DISTINCT
 10   rec_empno2;
 11  End;
 12  /

PL/SQL procedure successfully completed.

Twinkle

damorgan

Please do not refer people to the old unsupprted psoug site.

Morgan's Library has been relocated to www.morganslibrary.org with the main library page as www.morganslibrary.org/library.html.

The specific page you refer to is:
http://www.morganslibrary.org/reference/type.html
and has been updated several times since the ancient one was written.

Thank you for updating all of your links to the new site too.

Twinkle: Please use the edit button to update your link to the correct one. Thank you.

CGMS

Thanks for all of your answers.

But i need to merge the two INDEX BY tables.

is there any any way to do merge between two INDEX BY tables?


Suresh.

Tubby

Loop through one and append it to the other is about your only option (that i'm aware of).

MichaelS

declare
   type rec_nbrchk is table of emp.empno%type
                         index by binary_integer;

   rec_nbr_chk      rec_nbrchk;
   mlcl_scope_chk   rec_nbrchk;
   all_chk          rec_nbrchk;
begin
   for i in 1 .. rec_nbr_chk.count
   loop
      all_chk (i) := rec_nbr_chk (i);
   end loop;

   for i in 1 .. mlcl_scope_chk.count
   loop
      all_chk (i + rec_nbr_chk.count) := mlcl_scope_chk (i);
   end loop;
end;

CGMS

Hi,


The last post is very useful for me.

I want distinct records from these two INDEX BY tables.
Please let me know if there are any way to distinct all_chk INDEX BY table.

Suresh.

CGMS

Hi all,

i came to conclusion to find the DISTINCT records by following approach:

declare
type rec_nbrchk is table of emp.empno%type
index by binary_integer;

rec_nbr_chk rec_nbrchk;
mlcl_scope_chk rec_nbrchk;
all_chk rec_nbrchk;
chk_flg BOOLEAN;
idx NUMBER;
begin
for i in 1 .. rec_nbr_chk.count
loop
all_chk (i) := rec_nbr_chk (i);
end loop;

for i in 1 .. mlcl_scope_chk.count
loop
chk_flg := true;
idx:= 1;
while idx <= all_chk.count
loop
if all_chk(idx) = mlcl_scope_chk(i) then
chk_flg := false;
exit;
end if;
end loop;
if chk_flg = true then
all_chk (i + rec_nbr_chk.count) := mlcl_scope_chk (i);
end if;
end loop;
end;


is there any other ways to retrieve distinct records from joining of two INDEX BY tables?

Suresh.

MichaelS

But i need to merge the two INDEX BY tables.

But you could probably use an intermediate associate array?

SQL> select empno
  from emp
 where empno > 7500
/
     EMPNO
----------
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

12 rows selected.

SQL> select empno
  from emp
 where empno < 7700
/
     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698

6 rows selected.

SQL> declare
   type rec_nbrchk is table of emp.empno%type index by binary_integer;

   type all_chk_tab_tmp is table of emp.empno%type;

   rec_nbr_chk      rec_nbrchk;
   mlcl_scope_chk   rec_nbrchk;
   all_chk          rec_nbrchk;
   all_chk_tmp      all_chk_tab_tmp := all_chk_tab_tmp ();
begin
 /*  Just for demonstration purpose: Fill the arrays with some values */
   select empno
     bulk collect into rec_nbr_chk
     from emp
    where empno > 7500;

   select empno
     bulk collect into mlcl_scope_chk
     from emp
    where empno < 7700;

   for i in 1 .. rec_nbr_chk.count + mlcl_scope_chk.count
   loop
      all_chk_tmp.extend;
      all_chk_tmp (i) :=
         case
            when i <= rec_nbr_chk.count  then rec_nbr_chk (i)
            else mlcl_scope_chk (i - rec_nbr_chk.count )
         end;
   end loop;

   all_chk_tmp := all_chk_tmp multiset union distinct all_chk_tmp;

   for i in 1 .. all_chk_tmp.count
   loop
      all_chk (i) := all_chk_tmp (i);
      dbms_output.put_line( to_char(i,'fm00') || ': ' || all_chk (i));
   end loop;
end;
/
01: 7521
02: 7566
03: 7654
04: 7698
05: 7782
06: 7788
07: 7839
08: 7844
09: 7876
10: 7900
11: 7902
12: 7934
13: 7369
14: 7499
PL/SQL procedure successfully completed.