Oracle Listagg function helps to concatenate multiple rows into a single string. This article explains this in details.
Once a developer asked, “How to combine multiple rows into a single string delimited by comma, or pipe?” The requirement is to get email id from a table, concatenate those by “,” and use it as a parameter while sending an email.
“This is how it is, is there any better way?”
“Can we remove that for loop?”
Example: PL/QL function to group rows and return concatenated single string
SET SERVEROUTPUT ON CREATE OR REPLACE FUNCTION get_email RETURN VARCHAR2 IS l_mail VARCHAR2(1000) := NULL; BEGIN FOR i IN (SELECT email FROM demo_user ) LOOP l_mail := i.email ||','||l_mail; END LOOP; dbms_output.put_line('Email ' || l_mail); RETURN l_mail; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Exception in mail function :'||SQLCODE||SQLERRM); RETURN '-1'; END;
SELECT get_email email FROM dual; EMAIL -------------------------------------------------------------------------------- stu@myorg.com,mno@myorg.com,jkl@myorg.com,efg@myorg.com,lmn@myorg.com,xyz@myorg. com,pqr@myorg.com,abc@myorg.com,
Googled it and found a built-in Oracle function LISTAGG. This is available in Oracle Database 11G Release 2.
This made the job very easy. Entire PL/SQL logic is replaced with single SQL.
Example: – Listagg Oracle built-in function to return group of rows as single string
SELECT listagg(email,',') WITHIN GROUP (ORDER BY user_id) FROM demo_user; EMAIL -------------------------------------------------------------------------------- abc@myorg.com,pqr@myorg.com,xyz@myorg.com,lmn@myorg.com,efg@myorg.com,jkl@myorg. com,mno@myorg.com,stu@myorg.com
Listagg operates on a group of rows and returns as a single column with user-defined concatenate character.
In the above example, I used a comma (,). You can use a comma, semi-colon or any valid character.