title: How to output object with DBMS output in oracle from function return object in oracle
body: <p>I am written a function and object for employee details. The function returns an object for a specific employee and I want to show the result via DBMS output from the returned object. But I can't do this. Please help me to solve this problem.</p>
  
  <p>Types:</p>
  
  <pre><code>CREATE OR REPLACE TYPE EMP_DETAILS_OBJ AS OBJECT(
    EMPID NUMBER,
    ENAME VARCHAR2(100),
    DESIGNATION VARCHAR2(100),
    DEPARTMENT VARCHAR2(50),
    JOIN_DATE DATE,
    COUNTRY VARCHAR2(50),
    REGION VARCHAR2(30)
  )
  /
  
  CREATE OR REPLACE TYPE EMP_DETAILS_TABLE IS TABLE OF EMP_DETAILS_OBJ
  /
  </code></pre>
  
  <p>Function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION GETEMPLOYEE_INFO(SEARCH_EMPLOYEE_ID NUMBER)
  RETURN EMP_DETAILS_TABLE IS
    EMPLOYEEID NUMBER;
    EMPLOYEE_NAME VARCHAR2(100);
    DESIGNATION VARCHAR2(100);
    DEPARTMENT VARCHAR2(50);
    JOIN_DATE DATE;
    COUNTRY VARCHAR2(50);
    REGION VARCHAR2(30);
    EMP_DETAILS EMP_DETAILS_TABLE := EMP_DETAILS_TABLE();
  BEGIN
    EMP_DETAILS.EXTEND();
  
    SELECT EMP.EMPLOYEE_ID,
      EMP.FIRST_NAME||' '||EMP.LAST_NAME,
      EMPJOB.JOB_TITLE,
      DEPT.DEPARTMENT_NAME,
      EMP.HIRE_DATE,
      CNTRY.COUNTRY_NAME,
      RGN.REGION_NAME
    INTO EMPLOYEEID, EMPLOYEE_NAME, DESIGNATION, DEPARTMENT, JOIN_DATE,
      COUNTRY, REGION
    FROM EMPLOYEES EMP
    LEFT OUTER JOIN JOBS EMPJOB ON EMPJOB.JOB_ID = EMP.JOB_ID
    LEFT OUTER JOIN DEPARTMENTS DEPT ON DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID
    LEFT OUTER JOIN LOCATIONS LOC ON LOC.LOCATION_ID = DEPT.LOCATION_ID
    LEFT OUTER JOIN COUNTRIES CNTRY ON CNTRY.COUNTRY_ID = LOC.COUNTRY_ID
    LEFT OUTER JOIN REGIONS RGN ON RGN.REGION_ID = CNTRY.REGION_ID
    WHERE EMP.EMPLOYEE_ID = SEARCH_EMPLOYEE_ID ;
  
    EMP_DETAILS(1) := EMP_DETAILS_OBJ(EMPLOYEEID, EMPLOYEE_NAME, DESIGNATION,
      DEPARTMENT, JOIN_DATE, COUNTRY, REGION);
  
    RETURN EMP_DETAILS;
  END;
  /
  </code></pre>
  
  <p>Calls:</p>
  
  <pre><code>SET SERVEROUTPUT ON;
  DECLARE 
    --EMP_DETAILS EMP_DETAILS_TABLE :=EMP_DETAILS_TABLE();
    EMP_OBJ EMP_DETAILS_OBJ;
  BEGIN
    EMP_OBJ :=GETEMPLOYEE_INFO(100);
    --EMP_OBJ :=EMP_DETAILS_OBJ(EMP_DETAILS.EMPID,EMP_DETAILS.ENAME,EMP_DETAILS.DESIGNATION,EMP_DETAILS.DEPARTMENT,EMP_DETAILS.JOIN_DATE,EMP_DETAILS.COUNTRY,EMP_DETAILS.REGION);
  
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID: '||EMP_OBJ.EMPID);
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE_NAME: ');
    DBMS_OUTPUT.PUT_LINE('DESIGNATION: ');
    DBMS_OUTPUT.PUT_LINE('DEPARTMENT: ');
    DBMS_OUTPUT.PUT_LINE('JOIN DATE: '||EMP_DETAILS.JOIN_DATE);
    DBMS_OUTPUT.PUT_LINE('COUNTRY: '||EMP_DETAILS.COUNTRY);
    DBMS_OUTPUT.PUT_LINE('REGION: '||EMP_DETAILS.REGION); 
  END;
  /
  
  SELECT GETEMPLOYEE_INFO(100) FROM DUAL;
  </code></pre>