Convert Delimited Strings to Arrays in Oracle SQL Without Built‑In Limits

Oracle provides the built‑in function DBMS_UTILITY.UNCL_ARRAY for converting a delimited string to a one‑dimensional array, but it suffers from many constraints such as disallowing numeric first characters, supporting only commas as separators, and failing on certain special symbols.

To overcome these limitations, you can use a pure‑SQL solution that leverages hierarchical queries and string functions. The method accepts two bind variables: :P_STRING (the source string) and :P_SEPARATE (the delimiter you wish to use). It builds a temporary string surrounded by the delimiter, counts the number of occurrences, and then extracts each element with SUBSTR and INSTR inside a level‑by‑level loop.

SELECT SRTING_VALUE
FROM (
  SELECT SUBSTR(
    T.CA,
    INSTR(T.CA, :P_SEPARATE, 1, C.LV) + 1,
    INSTR(T.CA, :P_SEPARATE, 1, C.LV + 1) - (INSTR(T.CA, :P_SEPARATE, 1, C.LV) + 1)
  ) AS SRTING_VALUE
  FROM (
    SELECT :P_SEPARATE || SRTING_VALUE || :P_SEPARATE AS CA,
           LENGTH(SRTING_VALUE || :P_SEPARATE) - NVL(LENGTH(REPLACE(SRTING_VALUE, :P_SEPARATE)), 0) AS CNT
    FROM (
      SELECT :P_STRING SRTING_VALUE FROM DUAL
    )
  ) T,
  (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100) C
  WHERE C.LV <= T.CNT
);

Because the delimiter is supplied via :P_SEPARATE, you can define any character or string as the separator, eliminating the constraints of the built‑in function.

Typical use cases include parsing QR‑code data, returning multiple rows from a PL/SQL function without using collections, building dynamic WHERE clauses in Oracle Reports, and other scenarios where flexible string tokenisation is required.

The author notes that this technique has been applied extensively in production environments and shares it here for the benefit of other developers.