I have a few views that all follow the same pattern. They select a primary key from some main table and then a few semicolon seperated strings of captions of related rows over many-to-many relations. All columns that are used in joins are either primary keys or have a manually set index and are of type NUMBER(18). The columns that hold the captions are either VARCHAR2 or - rarely - CLOB. Usually the view will be joined with the main table to select between 1 and 50 rows of the main table.The creation of the semicolon seperated strings is done with the listagg function from Oracle 11gR2 because my research showed that this is the easiest and also most performant solution. There is just one problem: There could be so many related rows that the listagg function reaches the limit of 4000 chars of its VARCHAR2 result. I'm currently solving this by limiting the set that listagg operates on to 50 rows. This is just a random number that I've chosen, it should prevent errors and the users don't care for more than 50 results anyway. But of course, it's still ugly code.If anyone has any ideas how to improve the following code, I'd be very happy. I'm especially interested in improvements that lead to less complexity, more performance or a better solution for the 4000 chars limit.CREATE OR REPLACE VIEW MAILS_RELATED(  MAIL_ID,  CUSTOMERS,  PROJECTS,  [some more columns])ASSELECT m.MAIL_ID,       cust_list.CUSTOMERS,       proj_list.PROJECTS,       [some more columns]  FROM MAILS m LEFT OUTER JOIN (         SELECT inner.MAIL_ID,                LISTAGG(inner.CUSTOMER_NAME, '; ')                  WITHIN GROUP (ORDER BY inner.CUSTOMER_NAME) AS CUSTOMERS           FROM (                SELECT rel.MAIL_ID,                       cust.CUSTOMER_NAME,                       ROW_NUMBER() OVER                         (PARTITION BY rel.MAIL_ID ORDER BY cust.CUST_ID) AS RN                  FROM CUSTOMERS_MAILS rel INNER JOIN                       CUSTOMERS cust ON (cust.CUST_ID = rel.CUST_ID)                ) inner          WHERE inner.RN <= 50          GROUP BY inner.MAIL_ID       ) cust_list ON (cust_list.MAIL_ID = m.MAIL_ID) LEFT OUTER JOIN (         SELECT inner.MAIL_ID,                LISTAGG(inner.PROJECT_NAME, '; ')                  WITHIN GROUP (ORDER BY inner.PROJECT_NAME) AS PROJECTS           FROM (                SELECT rel.MAIL_ID,                       proj.PROJECT_NAME,                       ROW_NUMBER() OVER                         (PARTITION BY rel.MAIL_ID ORDER BY proj.PROJ_ID) AS RN                  FROM PROJECTS_MAILS rel INNER JOIN                       PROJECTS proj ON (proj.PROJ_ID = rel.PROJ_ID)                ) inner          WHERE inner.RN <= 50          GROUP BY inner.MAIL_ID       ) proj_list ON (proj_list.MAIL_ID = m.MAIL_ID) LEFT OUTER JOIN       [some more joins]
Length limited listagg for muliple m:n relations in a view
sql;oracle