Wow! I used a function from Oracle 11g R2!

As a developer that uses the database to just store and retrieve things, I was until recently of the opinion that no interesting features had entered the Oracle database from Release 9i R2 on. Heck, even the sophisticated OO-features were available in 8i!

But now I had do compile a report where values from several rows had to be concatenated with semicolons as delimiters. I did not want to do this PL/SQL (seemed to me like overkill, with loops and all). Luckily, I found the funtion listagg, which does exactly that: Listagg-Documentation from Oracle.

Here is an example that uses listagg to give a report of all Orders (one Order per line) with all attached short-texts seperated by semicolons in the column "short_texts".

select
fadf.pk1_value as oe_header_id, listagg(fdst.short_text,';') within group (order by fadf.seq_num) as short_texts
from fnd_documents_short_text fdst
join fnd_attached_docs_form_vl fadf on (fadf.function_name='OEXOEORD' and fdst.media_id=fadf.media_id)
join fnd_documents fd on (fd.document_id = fadf.document_id) group by fadf.pk1_value;

In reality, you will have to restrict this query to just a few Orders - unless you are willing to wait for  a long time....

So, from time to time, I will actually look for new features in Oracle DB!