CREATE OR REPLACE FUNCTION VALOR_NA_LISTA
(
  P_VALOR IN VARCHAR2,
  P_LISTA IN VARCHAR2,
  P_DELIMIT IN VARCHAR2 DEFAULT '_',
  P_TODOS IN VARCHAR2 DEFAULT 'T'
)
RETURN NUMBER
AS
V_EXISTE NUMBER;
BEGIN
  IF (P_LISTA = P_TODOS) THEN
    RETURN 1;
  END IF;
  
  IF (SUBSTR(P_LISTA, 0, 9) = '_NRLISTA=') THEN
    SELECT COUNT(1) INTO V_EXISTE 
      FROM LISTA
        WHERE NRLISTA = SUBSTR(P_LISTA, 10)
          AND CDLISTA = P_VALOR;    
    RETURN V_EXISTE;
  END IF;
  
  SELECT COUNT(1) INTO V_EXISTE 
    FROM DUAL
   WHERE REGEXP_SUBSTR(P_LISTA,'[^'||P_DELIMIT||']+', 1, LEVEL) = P_VALOR
   CONNECT BY REGEXP_SUBSTR(P_LISTA, '[^'||P_DELIMIT||']+', 1, LEVEL) IS NOT NULL;

  RETURN V_EXISTE;
END;
/