Introduction
In enterprise applications, it’s common to store related values across multiple rows, order items, user roles, error messages, or tags. Presenting these as a single, comma-separated string makes reports and exports far more readable. Prior to Oracle Database 11g Release 2, developers resorted to complex, verbose hacks, nested SYS_CONNECT_BY_PATH calls or clunky XMLAGG constructs. Oracle LISTAGG changed that by offering a concise aggregate function to flatten multi-row values into a neatly ordered string. In this article, we’ll explore how to leverage Oracle LISTAGG in PL/SQL blocks and stored procedures, examining syntax, ordering, dynamic use, and performance considerations. By the end, you’ll know how to elegantly concatenate rows into human-friendly text and integrate LISTAGG seamlessly into your PL/SQL toolkit.
Understanding the LISTAGG Syntax
At its core, LISTAGG takes two main arguments: the column to concatenate and the delimiter between values. You then specify an ORDER BY clause within the WITHIN GROUP construct to control sequence:
sql
CopyEdit
LISTAGG(column_name, ‘, ‘)
WITHIN GROUP (ORDER BY column_name)
- column_name: The values to join.
- ‘, ‘: Commonly a comma and space for readability.
- WITHIN GROUP (ORDER BY …): Ensures consistent ordering of concatenated elements.
For example, to list employees in each department:
sql
CopyEdit
SELECT department_id,
LISTAGG(employee_name, ‘, ‘)
WITHIN GROUP (ORDER BY employee_name) AS employees
FROM employees
GROUP BY department_id;
This returns one row per department, with a neatly ordered employee list.
Embedding LISTAGG in PL/SQL
While you can use LISTAGG in plain SQL, wrapping it in PL/SQL adds flexibility. Here’s an anonymous block that captures the concatenated result into a variable:
plsql
CopyEdit
DECLARE
v_employee_list VARCHAR2(4000);
BEGIN
SELECT LISTAGG(employee_name, ‘, ‘)
WITHIN GROUP (ORDER BY employee_name)
INTO v_employee_list
FROM employees
WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE(‘Dept 10 Employees: ‘ || v_employee_list);
END;
This approach is ideal for dynamic reporting, logging combined messages in audit logs, or export routines that pass concatenated strings to external files or APIs.
Handling Large Strings and Limits
Oracle limits VARCHAR2 strings to 4000 bytes in SQL and 32767 in PL/SQL. If LISTAGG output exceeds this, you’ll encounter ORA-01489. To guard against this:
- Filter rows before aggregation: Use WHERE clauses to limit entries.
- Truncate values: Apply SUBSTR on individual elements if they tend to be long.
Use ON OVERFLOW TRUNCATE (Oracle 12c R2+):
sql
CopyEdit
LISTAGG(comment_text, ‘, ‘
ON OVERFLOW TRUNCATE WITH COUNT)
WITHIN GROUP (ORDER BY comment_date)
- This safely cuts the output to fit, appending “… (n more)” to indicate truncated items.
Dynamic LISTAGG with Distinct Values
Since LISTAGG lacks a DISTINCT option, you can achieve uniqueness through a subquery:
sql
CopyEdit
SELECT department_id,
LISTAGG(employee_name, ‘, ‘)
WITHIN GROUP (ORDER BY employee_name) AS unique_employees
FROM (
SELECT DISTINCT department_id, employee_name
FROM employees
)
GROUP BY department_id;
To handle varying departments dynamically within PL/SQL:
plsql
CopyEdit
DECLARE
CURSOR c_depts IS SELECT DISTINCT department_id FROM employees;
v_list VARCHAR2(32767);
BEGIN
FOR r IN c_depts LOOP
SELECT LISTAGG(employee_name, ‘, ‘)
WITHIN GROUP (ORDER BY employee_name)
INTO v_list
FROM (
SELECT DISTINCT employee_name
FROM employees
WHERE department_id = r.department_id
);
DBMS_OUTPUT.PUT_LINE(‘Dept ‘ || r.department_id || ‘: ‘ || v_list);
END LOOP;
END;
Best Practices for Production Use
- Sanitize inputs in dynamic PL/SQL to avoid SQL injection.
- Index the ORDER BY column to optimize sort performance.
- Limit row count when possible, especially in high-volume reporting.
- Monitor execution plans; heavy aggregations may benefit from materialized views.
- Use meaningful delimiters; consider semicolons or pipes if values contain commas.
Conclusion
Oracle LISTAGG brings clarity and simplicity to the once-clunky task of concatenating multiple row values into a single string. When combined with PL/SQL’s procedural power, you can automate human-readable output for reports, logs, or exports, without resorting to XML hacks or recursive hierarchies. By understanding its syntax, limits, and best practices, you’ll master elegant row concatenation and overcome potential pitfalls. Whether you’re building dashboards, audit trails, or data feeds, LISTAGG is your go-to tool for turning vertical row sets into clean, horizontal narratives.