19c LISTAGG Can Do Distinct

Do you use analytic functions in Oracle? I do not use them as much as I’d like to, but I love analytic functions, as they really transform the queries, the resulting reports and most of all they make our life easier!
My favorite analytic function is LISTAGG.
I few years ago, I wrote a blog post about LISTAGG, as it helped me a lot in scripting to check some privileges. You can check that out here: How To Use LISTAGG To Create Awesome DBA Reports.

In 19c, Oracle introduced the distinct feature for the LISTAGG function, eliminating duplicate values from the resulting string. Why is this a good thing? Because it removes the need to write complex queries, to try and accomplish the same results.

The LISTAGG function is aggregating a list of results for a column, in a specified sequence within a group. For example I want to list the privileges assigned to a role in the following format. For this exercise, I only want to see the high level permissions, I am not interested in the object level privileges that are granted.

ROLE_NAME		       PRIVILEGES
------------------------------ ----------------------------------
APPS_ADMIN		       DELETE,INSERT,SELECT,UPDATE

Let’s say this role has the following grants:

select table_name, privilege 
from dba_tab_privs
where grantee='APPS_ADMIN';

TABLE_NAME	PRIVILEGE
--------------	-----------
TEST_TBL1	SELECT
TEST_TBL1	UPDATE
TEST_TBL1	DELETE
TEST_TBL2	SELECT
TEST_TBL2	INSERT

If I were to list the privileges with the LISTAGG, prior to 19c, the results would look like this:

select grantee as role_name, listagg(privilege,',') 
       within group (order by privilege) as privileges
from dba_tab_privs
where grantee='APPS_ADMIN'
group by grantee;

ROLE_NAME		       PRIVILEGES
------------------------------ ----------------------------------
APPS_ADMIN		       DELETE,INSERT,SELECT,SELECT,UPDATE

Do you notice the duplicate entry for the select statement? I kind of want to eliminate that duplication. Prior to 19c you would have had to write complex queries to eliminate these duplicates.
In 19c this is much easier to do:

select grantee as role_name, listagg(distinct privilege,',') within group (order by privilege) as privileges
from dba_tab_privs
where grantee='APPS_ADMIN'
group by grantee;

ROLE_NAME		       PRIVILEGES
------------------------------ ----------------------------------
APPS_ADMIN		       DELETE,INSERT,SELECT,UPDATE

Do you use LISTAGG a lot? What analytic functions do you like to use?

Leave a comment below and let me know! I read every comment!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the best price, only $18.99 CAD!