Hi all,
set serveroutput on;
DECLARE
--TYPE t_tab IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
l_tab1 test_api.t_tab;
l_tab2 test_api.t_tab;
BEGIN
SELECT *
BULK COLLECT INTO l_tab1
FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.put_line('Loop Through Collection');
FOR cur_rec IN (SELECT *
FROM TABLE(l_tab1)
)
LOOP
DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename|| ' : ' || cur_rec.sal);
END LOOP;
DBMS_OUTPUT.put_line('---------------');
SELECT *
BULK COLLECT INTO l_tab2
FROM emp
WHERE deptno = 20;
--l_tab1 := l_tab1 multiset union l_tab2;
FOR cur_rec IN (SELECT *
FROM TABLE(l_tab2)
order by sal
)
LOOP
DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename|| ' : ' || cur_rec.sal);
END LOOP;
END;
Result of the program is
Loop Through Collection
7782 : CLARK : 2450
7839 : KING : 5000
7934 : MILLER : 1300
---------------
7369 : SMITH : 800
7876 : ADAMS : 1100
7566 : JONES : 2975
PL/SQL procedure successfully completed.
Is there any way i can append l_tab1 with l_tab12. I tried with
quote
_tab1 := l_tab1 multiset union l_tab2;
unquote
but it is giving me below error
Error report -
ORA-06550: line 26, column 14:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_ALL'
ORA-06550: line 26, column 14:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_ALL'
ORA-06550: line 26, column 4:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Could you please help.
Regards
Rajat