Dear All - I have shown a sample code below which works on scott.emp table and database is 19c. The need with help of this code is to understand how to use cast multiset and member of usages.
In my case, I am populating the collection multiple times in the ATP database using multiple web service calls, and then with help of collection data need to update a table which is the emp table in the demo case. I have searched google but couldn't make any of the below update statements work so looking for your expert suggestions.
declare
type rt is record(empno varchar2(10)
, hiredate date
, comm number);
type tt is table of rt;
l_emp tt := tt();
begin
select empno, hiredate, comm bulk collect into l_emp
from emp
where deptno = 10;
update emp e
set comm = cast(multiset(
select NVL(column_value.comm, 100)
from table (l_emp)
WHERE column_value.comm IS NULL
) as comm)
;
update emp
set comm = NVL(l_emp.comm, 100)
where (deptno, empno) MEMBER OF l_emp;
end;
/