Oracle function return Array of Varchar2
To return an array from a function, we will store the array inside database.
Oracle will raise compilation error if we don't store the data inside DB.
Setup for return an array of Varchar2 from function:
Creating a record type:
CREATE OR REPLACE TYPE VAR_REC_TYPE IS OBJECT (VAR_STRING VARCHAR2(500)); /
Creating a table type of record type VAR_REC_TYPE:
CREATE OR REPLACE TYPE VAR_TAB_TYPE IS TABLE OF VAR_REC_TYPE; /
Creating the function:
--Oracle function return Array of Varchar2 : Example
CREATE OR REPLACE FUNCTION FUN_RET_VARCHAR(P_DEPTNO DEPARTMENTS.DEPARTMENT_ID%TYPE)
RETURN VAR_TAB_TYPE
IS
V_VAR_TYPE VAR_TAB_TYPE := VAR_TAB_TYPE();
BEGIN
SELECT VAR_REC_TYPE('EMPLOYEE_ID -> '||A.EMPLOYEE_ID||' -> '||A.FIRST_NAME||' '||A.LAST_NAME||' WORKS IN '||B.DEPARTMENT_NAME||' DEPARTMENT')
BULK COLLECT INTO V_VAR_TYPE
FROM EMPLOYEES A,
DEPARTMENTS B
WHERE A.DEPARTMENT_ID = P_DEPTNO
AND A.DEPARTMENT_ID = B.DEPARTMENT_ID;
--
RETURN V_VAR_TYPE;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/
Calling the function:
--Oracle function return Array of Varchar2 : Example calling
SET SERVEROUTPUT ON;
DECLARE
V_VAR_TYPE VAR_TAB_TYPE := VAR_TAB_TYPE();
BEGIN
V_VAR_TYPE := FUN_RET_VARCHAR(30);
--
FOR I IN 1..V_VAR_TYPE.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(V_VAR_TYPE(I).VAR_STRING);
END LOOP;
END;
/
RELATED TOPICS:
