CREATE OR REPLACE FUNCTION COMPLEMENTARA_CARDBASE
(P_NRCARDAPIOBASE IN NUMBER) RETURN VARCHAR2
AS
   V_CARDAPIOSB VARCHAR2(1000);
BEGIN
   V_CARDAPIOSB := '';
   FOR C_CARDS IN
   (SELECT CBCOMPL.CDCARDAPIOBASE
      FROM CARDAPIOBASE C      
           INNER JOIN CARDCOMPLCARDBASE CCOMPL ON CCOMPL.NRCARDAPIOBASE = C.NRCARDAPIOBASE
           INNER JOIN CARDAPIOBASE CBCOMPL ON CBCOMPL.NRCARDAPIOBASE = CCOMPL.NRCARDAPIOBASECOMPL
     WHERE C.NRCARDAPIOBASE = P_NRCARDAPIOBASE
     ORDER BY CBCOMPL.CDCARDAPIOBASE
   ) LOOP
      --Separador de valores
      IF LENGTH(V_CARDAPIOSB) > 0 THEN
         V_CARDAPIOSB := V_CARDAPIOSB || ', ';
      END IF;
      -- Terminador caso limite tenha sido atingido
      IF LENGTH(V_CARDAPIOSB) + LENGTH(C_CARDS.CDCARDAPIOBASE) > 1000 THEN         
         V_CARDAPIOSB := V_CARDAPIOSB || SUBSTR(C_CARDS.CDCARDAPIOBASE, 1, 995) || '(...)';
      ELSE
         V_CARDAPIOSB := V_CARDAPIOSB || C_CARDS.CDCARDAPIOBASE;
      END IF;
   END LOOP;
   RETURN V_CARDAPIOSB;
END;
/