SQL & PL/SQL

Hi all,

I am trying to use MULTISET EXCEPT to exclude few elements in a main collection. Here is the code. When I try to execute this, I get "PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'" exception. Could you please help me to resolve the issue?

Thanks in advance!



CREATE OR REPLACE Type emp_copy_obj as Object(e_no INTEGER, e_name VARCHAR2(50));
/
create or replace type ty_emp_copy is table of emp_copy_obj;
/

DECLARE
tb_emp_copy_1 ty_emp_copy;
tb_emp_copy_2 ty_emp_copy := ty_emp_copy();
tb_emp_copy_3 ty_emp_copy := ty_emp_copy();
BEGIN
SELECT emp_copy_obj(empno, ename)
BULK COLLECT INTO tb_emp_copy_1
FROM emp
WHERE empno IN (1, 2, 3, 4);

FOR i IN tb_emp_copy_1.FIRST .. tb_emp_copy_1.LAST
LOOP
IF i IN (1, 2) THEN
tb_emp_copy_2.EXTEND;
tb_emp_copy_2(tb_emp_copy_2.LAST) := tb_emp_copy_1(i);
END IF;
END LOOP;

dbms_output.put_line(tb_emp_copy_1.COUNT);
dbms_output.put_line(tb_emp_copy_2.COUNT);

tb_emp_copy_3 := tb_emp_copy_1 MULTISET except tb_emp_copy_2;

dbms_output.put_line('-------------');

dbms_output.put_line(tb_emp_copy_1.COUNT);
dbms_output.put_line(tb_emp_copy_2.COUNT);
dbms_output.put_line(tb_emp_copy_3.COUNT);

IF tb_emp_copy_3.COUNT > 0 THEN
FOR i IN tb_emp_copy_3.FIRST .. tb_emp_copy_3.LAST
LOOP
dbms_output.put_line(tb_emp_copy_3(i).e_no || ' - ' || tb_emp_copy_3(i).e_name);
END LOOP;
END IF;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error - ' || SQLERRM);
END;
/

PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'