Return Multiple Value From a PLSQL Function in Oracle

Topic Introduction: Today we will discuss how to Return Multiple Value From a PLSQL Function in Oracle. Generally, we know that function must return values where the procedure may return or not. as usual we see the function return a single value if we want to return multiple values it's also possible. If we want to return value need to use Table Type.

Here we will follow 4 steps to get return multiple values from a PLSQL Function in Oracle

• Create a collection-type object
• Create a table-type object
• Create a Function to Return the Type as a table
• Call Function to return multiple values

Create a Collection Type Object:

CREATE OR REPLACE TYPE EMP_TYPE_O AS OBJECT

(

FIRST_NAME VARCHAR2(15),

LAST_NAME VARCHAR2(12)

);

Create a Table Type Object:

CREATE TYPE EMP_TYPE_T IS TABLE OF EMP_TYPE_O ;

Create a function:

CREATE OR REPLACE FUNCTION get_emp_list (P_ID NUMBER)

    RETURN emp_type_t

AS

    v_ename   emp_type_t;

BEGIN

    SELECT emp_type_o (FIRST_NAME, LAST_NAME)

      BULK COLLECT INTO v_ename

      FROM EMPLOYEES

     WHERE DEPARTMENT_ID = P_ID;

    RETURN v_ename;

END;

Call Function to return multiple values:

SELECT * FROM TABLE ( GET_EMP_LIST (20));