Length limited listagg for muliple m:n relations in a view
Oracle regular Character types are limited to 4000 bytes. Really, Oracle should find a way to extend that, but until they do, using CLOB is the only real option.

On the other hand, Oracle has a rich infrastructure available for creating user defined functions. You are already creating the view, you may as well extend that to include the function.

There are a number of articles out there that can help:


http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php
http://www.oracle-developer.net/display.php?id=306
and others....


The one that is missing from those is the use of XML functions.... It is a relatively commonly used 'hack', to use the XML-processing functions in Oracle (and other databases) to reformat the data in a more convenient way, and then to extract from that XML just the parts you want.

So, ideally you should create a user-defined function for your problem, but, you can accomplish the same thing, perhaps faster, with a hack of XML....

Consider swapping the nested select:


     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 &lt;= 50
      GROUP BY inner.MAIL_ID



with:

Select CM.MAIL_ID,
  substr(xmlcast(
    xmlagg(
      xmlelement(E, '; ' || C.CUSTOMER_NAME)
      ORDER BY C.CUSTOMER_NAME
    ) AS CLOB -- or AS VARCHAR2(4000)
  ), 3
) as CUSTOMERS
from CUSTOMERS C, CUSTOMER_MAIL CM
where C.CUST_ID = CM.CUST_ID
group by CM.MAIL_ID


I have put together a simple SQLFiddle that shows this select in action. It assumes two sets of users, (some male names, some female names), and it puts the different users in to different MAIL_ID's.

Once you have swapped out these nested statements, it will significantly shorten your query.... Your handling code though will have to change. Getting a CLOB back from the view is a big change.... and it is best to handle that in a different way....

Edit: About this operation as a general problem

At an academic level, the problem you are experiencing is because you are doing a non-relational operation in a relational database. Data in a relational database is treated using set arithmetic. You have sets of data that you can intersect, union, filter, and otherwise manipulate. The problem you are tying to solve in this question is the conversion of a set of values in to a single merged value (with internal consistency requirements). This type of operation is not well defined in set theory.

The standard/recommended mechanism for solving this problem is to export the data set in to an external application, and process the data from there. Any system you use in an SQL query to remove the set-like nature of the data is a 'hack', and that is why you are unsatisfied with the result you are seeing. The right solution is to not use SQL.