CREATE OR REPLACE FUNCTION SEGMENTO_ADESAO(P_NRADESAOCARDBASE IN NUMBER) RETURN VARCHAR2
AS
   V_SEGMENTOS VARCHAR2(1000);
BEGIN
   V_SEGMENTOS := '';
   FOR C_SEGS IN
   (SELECT S.DSSEGMER
      FROM SEGMERADESAOCBASE SC
           INNER JOIN SEGMERCADO S ON S.CDSEGMER = SC.CDSEGMER
     WHERE SC.NRADESAOCARDBASE = P_NRADESAOCARDBASE
     ORDER BY S.DSSEGMER) LOOP
      --Separador de valores
      IF LENGTH(V_SEGMENTOS) > 0 THEN
         V_SEGMENTOS := V_SEGMENTOS || ', ';
      END IF;
      -- Terminador caso limite tenha sido atingido
      IF LENGTH(V_SEGMENTOS) + LENGTH(C_SEGS.DSSEGMER) > 1000 THEN         
         V_SEGMENTOS := V_SEGMENTOS || SUBSTR(C_SEGS.DSSEGMER, 1, 995) || '(...)';
      ELSE
         V_SEGMENTOS := V_SEGMENTOS || C_SEGS.DSSEGMER;
      END IF;
   END LOOP;
   RETURN V_SEGMENTOS;
END;
/