object type.sql

CREATE OR REPLACE TYPE nte2
AUTHID DEFINER IS
RECORD (
  emp_id     NUMBER(6),
  lastname   VARCHAR2(25),
  job        VARCHAR2(10),
  sal        NUMBER(8,2)
);
/

-- Created type of nested table:
 
CREATE OR REPLACE TYPE emp_list_ IS
  TABLE OF nte;
/

CREATE OR REPLACE TYPE MANAGER_INFO IS 
OBJECT( MEINFO nte,
        ELIST EMP_LIST_);
        
CREATE OR REPLACE TYPE MLIST IS TABLE OF MANAGER_INFO; 


--Nested table view practice   
SELECT M.EMPLOYEE_ID,M.LAST_NAME,M.JOB_ID,M.SALARY,
CAST(MULTISET(
SELECT E.EMPLOYEE_ID ,E.LAST_NAME ,E.JOB_ID,E.SALARY FROM EMPLOYEES E
WHERE E.MANAGER_ID = M.EMPLOYEE_ID
)AS EMP_LIST_ )ELIST
FROM EMPLOYEES M WHERE M.EMPLOYEE_ID IN 
(SELECT DISTINCT E2.MANAGER_ID FROM EMPLOYEES E2)

      
CREATE OR REPLACE VIEW MANAGER_EMPLOYEE_VIEW 
AS         
SELECT M.EMPLOYEE_ID,M.LAST_NAME,M.JOB_ID,M.SALARY,
CAST(MULTISET(
SELECT E.EMPLOYEE_ID ,E.LAST_NAME ,E.JOB_ID,E.SALARY FROM EMPLOYEES E
WHERE E.MANAGER_ID = M.EMPLOYEE_ID
)AS EMP_LIST_ ) ELIST
FROM EMPLOYEES M WHERE M.EMPLOYEE_ID IN 
(SELECT DISTINCT E2.MANAGER_ID FROM EMPLOYEES E2);

SELECT * FROM MANAGER_EMPLOYEE_VIEW V;
SELECT * FROM TABLE(SELECT V.ELIST FROM MANAGER_EMPLOYEE_VIEW V WHERE V.EMPLOYEE_ID = 100);

declare
ml mlist;
begin
  SELECT M.EMPLOYEE_ID,M.LAST_NAME,M.JOB_ID,M.SALARY bulk collect into ml.MEINFO
FROM EMPLOYEES M WHERE M.EMPLOYEE_ID IN 
(SELECT DISTINCT E2.MANAGER_ID FROM EMPLOYEES E2);

end;