Introduction
This Post is about to Map Member Functions For Multiset Operations in Oracle EBS R12.MAP member functions are special functions used to compare objects. The MAP member functions are used for performing comparisons between a single attribute of an object instance to a unique attribute of another object instance.
A sample object type with a MAP member function
Script to Map Member Functions For Multiset Operations
Step 1: Create Object
CREATE OR REPLACE TYPE obj_emp AS OBJECT (
ename VARCHAR2 (20),
dep NUMBER,
MAP MEMBER FUNCTION elist
RETURN RAW
);
/
Step 2: Create Type
CREATE OR REPLACE TYPE ntyp_emp IS TABLE OF obj_emp; /
Step 3: Create Type Body
CREATE OR REPLACE TYPE BODY obj_emp
AS
MAP MEMBER FUNCTION list
RETURN RAW
IS
BEGIN
RETURN UTL_RAW.cast_to_raw (SELF.ename || SELF.dep);
END;
END;
/
For a complex type of object (row type), need to provide MAP function to work with all MULTISET operations. Multiset operators combine the results of two nested tables into a single nested table.
A sample code for using Multiset operators
DECLARE
lt_emp_dtls ntyp_emp;
lt_emp_per_dtls ntyp_emp;
lt_emp_distinct ntyp_emp;
lt_emp_union ntyp_emp;
lt_emp_intersect ntyp_emp;
BEGIN
lt_emp_dtls := ntyp_emp (obj_emp (NULL, NULL));
lt_emp_per_dtls := ntyp_emp (obj_emp (NULL, NULL));
lt_emp_distinct := ntyp_emp (obj_emp (NULL, NULL));
lt_emp_union := ntyp_emp (obj_emp (NULL, NULL));
lt_emp_intersect := ntyp_emp (obj_emp (NULL, NULL));
BEGIN
SELECT obj_emp (ename, did)
BULK COLLECT INTO lt_emp_dtls
FROM emp_details;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Error In emp details’);
END;
BEGIN
SELECT obj_emp (ename, did)
BULK COLLECT INTO lt_emp_per_dtls
FROM emp_per_dtls;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Error In emp personal details’);
END;
lt_emp_union := lt_emp_dtls MULTISET UNION lt_emp_per_dtls;
FOR i IN 1 .. lt_emp_union.COUNT
LOOP
DBMS_OUTPUT.put_line (‘Multiset Union ‘ || lt_emp_union (i).ename);
END LOOP;
lt_emp_intersect := lt_emp_dtls MULTISET INTERSECT lt_emp_per_dtls;
FOR i IN 1 .. lt_emp_intersect.COUNT
LOOP
DBMS_OUTPUT.put_line (‘Multiset Intersect ‘ || lt_emp_intersect (i).ename
);
END LOOP;
lt_emp_distinct := lt_emp_dtls MULTISET EXCEPT DISTINCT lt_emp_per_dtls;
FOR i IN 1 .. lt_emp_distinct.COUNT
LOOP
DBMS_OUTPUT.put_line (‘Multiset Distinct ‘ || lt_emp_distinct (i).ename);
END LOOP;
END;
Output:
Multiset Union Ram
Multiset Union John
Multiset Union Jasmin
Multiset Union Raj
Multiset Union Rose
Multiset Union Jasmin
Multiset Union Rose
Multiset Union Raj
Multiset Intersect Jasmin
Multiset Intersect Raj
Multiset Intersect Rose
Multiset Distinct Ram
Multiset Distinct John
PL/SQL procedure successfully completed.
What we expect in the script.
This script helps us to MAP member functions are special functions used to compare objects. The MAP member functions are used for performing comparisons between a single attribute of an object instance to a unique attribute of another object instance.
Summary
This Post described the script Map Member Functions For Multiset Operations in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
