Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 395 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
How to merge two INDEX BY tables?

CGMS
Member Posts: 68
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.
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.
Tagged:
Answers
-
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 -
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 -
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. -
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. -
Loop through one and append it to the other is about your only option (that i'm aware of).
-
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;
-
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. -
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. -
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.
This discussion has been closed.