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: