Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Collection - CAST(Multiset) and MEMBER OF

Orcl ApexApr 8 2022

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;
/
Comments
Post Details
Added on Apr 8 2022
15 comments
1,011 views