How to write store procedure with object type as out parameter in Pl/SQL with multiple joins

Question

I am trying to write a store procedure with below sample use case and tables,

Employee
Emp_id Emp_Name 1 Jhon 2 Mark 3 Marry

Department
Emp_Id Dept_Id 1 A 2 B 3 C 1 B 2 D

Assets Emp_Id Asset_Name 1 AA 1 BB 2 CC 2 DD 4 EE 4 FF

Relationship

One employee can be added to more than one department. e.g Emp 1 added to A and B Department.

One Employee Can have more than one Assets e.g Emp 1 owing Assets AA and BB.

No Foreign key constraint between Employee And Assets. So Assets can have EmpId which is not available in Employee Table. e.g Emp 4

Desired output

EmployeeInfo Emd_Id Emp_Name Array of Dept_Id[] Array of Assets[]

Desired Result for employee Id 1

Emd_Id :1 Emp_Name :Jhon Array of Dept_Id[] :[A,B] Array of Assets[] :[AA,BB]

Desired Result for employee Id 4

Emd_Id :4 Emp_Name :null -- As no entry in Employee table. Array of Dept_Id[] :null Array of Assets[] :[EE,FF]

So want write a store procedure for this.Please suggest solution for this. Either this can be achieved with multiple cursor or object type out variable?

Stored Procedure I tried as below,

CREATE OR REPLACE PROCEDURE PRC_TEST( employeeInfo OUT SYS_REFCURSOR) IS BEGIN OPEN employeeInfo FOR SELECT e.EMP_ID ,e.EMP_NAME, d.DEPT_ID, a.ASSET_NAME FROM EMPLOYEE e, DEPARTMENT d, ASSETS a WHERE e.EMP_ID = d.EMP_ID AND e.EMP_ID = a.EMP_ID; END; Thanks in advance

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Employee ( Emp_id, Emp_Name ) AS
SELECT 1, 'Jhon' FROM DUAL UNION ALL
SELECT 2, 'Mark' FROM DUAL UNION ALL
SELECT 3, 'Marry' FROM DUAL
/

CREATE TABLE Department ( Emp_Id, Dept_Id ) AS
SELECT 1, 'A' FROM DUAL UNION ALL
SELECT 2, 'B' FROM DUAL UNION ALL
SELECT 3, 'C' FROM DUAL UNION ALL
SELECT 1, 'B' FROM DUAL UNION ALL
SELECT 2, 'D' FROM DUAL
/

CREATE TABLE Assets ( Emp_Id, Asset_Name ) AS
SELECT 1, 'AA' FROM DUAL UNION ALL
SELECT 1, 'BB' FROM DUAL UNION ALL
SELECT 2, 'CC' FROM DUAL UNION ALL
SELECT 2, 'DD' FROM DUAL UNION ALL
SELECT 4, 'EE' FROM DUAL UNION ALL
SELECT 4, 'FF' FROM DUAL
/

CREATE TYPE StringLIst IS TABLE OF VARCHAR2(20)
/

CREATE TYPE Emp_Dept_Assets_Obj AS OBJECT(
  Emp_id   INTEGER,
  Emp_Name VARCHAR2(50),
  Depts    StringList,
  Assets   StringList
)
/

CREATE FUNCTION get_Details(
  i_emp_id  IN  Employee.EMP_ID%TYPE
) RETURN Emp_Dept_Assets_Obj
IS
  o_details Emp_Dept_Assets_Obj;
BEGIN
  o_details := Emp_Dept_Assets_Obj( i_emp_id, NULL, NULL, NULL );

  BEGIN
    SELECT Emp_Name
    INTO   o_details.Emp_Name
    FROM   Employee
    WHERE  emp_id = i_emp_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;

  SELECT dept_id
  BULK COLLECT INTO o_details.Depts
  FROM   Department
  WHERE  emp_id = i_emp_id;

  SELECT asset_name
  BULK COLLECT INTO o_details.Assets
  FROM   Assets
  WHERE  emp_id = i_emp_id;

  RETURN o_details;
END;
/

Query 1:

SELECT d.details.emp_id,
       d.details.emp_name,
       d.details.depts,
       d.details.assets
FROM   (
  SELECT get_Details( LEVEL ) AS details
  FROM   DUAL
  CONNECT BY LEVEL <= 4
) d

Results:

| DETAILS.EMP_ID | DETAILS.EMP_NAME | DETAILS.DEPTS | DETAILS.ASSETS |
|----------------|------------------|---------------|----------------|
|              1 |             Jhon |           A,B |          AA,BB |
|              2 |             Mark |           B,D |          CC,DD |
|              3 |            Marry |             C |                |
|              4 |           (null) |               |          EE,FF |