Sayantan's Blog On Oracle

How to convert Array to String in Oracle?

We can convert an array to string both in SQL and PLSQL.

SQL Conversion:

In Oracle SQL we have a predefined aggregate function LISTAGG which can be used to convert an Array to String with specified format.

--How to convert Array to String in Oracle? : Example

SELECT DEPARTMENT_ID, LISTAGG(LAST_NAME, ',') WITHIN GROUP(ORDER BY EMPLOYEE_ID) AS EMP_LIST
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20, 30, 60)
GROUP BY DEPARTMENT_ID;

--How to convert Array to String in Oracle? : Example

SELECT DEPARTMENT_ID, LISTAGG(LAST_NAME, ' / ') WITHIN GROUP(ORDER BY EMPLOYEE_ID) AS EMP_LIST
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20, 30, 60)
GROUP BY DEPARTMENT_ID;

PLSQL Conversion:

--How to convert Array to String in Oracle? : Example

SET SERVEROUTPUT ON;
DECLARE
  TYPE T_DEPTNO IS TABLE OF DEPARTMENTS.DEPARTMENT_ID%TYPE INDEX BY PLS_INTEGER;
  V_DEPTNO T_DEPTNO;
  --
  TYPE T_EMP_FNAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_EMP_FNAME T_EMP_FNAME;
  --
  TYPE T_EMP_LNAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_EMP_LNAME T_EMP_LNAME;
  --
  V_EMP_NAME VARCHAR2(10000) := NULL;
BEGIN
  SELECT DEPARTMENT_ID
  BULK COLLECT INTO V_DEPTNO
  FROM DEPARTMENTS
  WHERE DEPARTMENT_ID IN (10, 20, 30, 60)
  ORDER BY DEPARTMENT_ID;
  --
  FOR I IN 1..V_DEPTNO.COUNT
  LOOP
    SELECT FIRST_NAME,
           LAST_NAME BULK COLLECT INTO V_EMP_FNAME,
                                       V_EMP_LNAME 
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = V_DEPTNO(I);
    --
    V_EMP_NAME := NULL;
    --
    FOR J IN 1..V_EMP_FNAME.COUNT
    LOOP
      V_EMP_NAME := V_EMP_NAME||V_EMP_FNAME(J)||' '||V_EMP_LNAME(J)||',';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID -> '||V_DEPTNO(I)||' -> NAME -> '||V_EMP_NAME);
  END LOOP;
  --
END;
/

RELATED TOPICS:

• Oracle Collection in PLSQL