Validate Cpf’s from a query

Question

Opa gente, to trying to validate the cpfs of a query, are several Cpf"s. I have the following function to validate Cpf, but I’m not getting call-there

I’m trying to do it like this: SELECT CPF_VALIDACAO(NUMR_CPF) FROM PESSOAS. but it doesn’t work. Someone would know the right way to call this function?

CREATE OR REPLACE FUNCTION CPF_VALIDACAO
      (p_cpf     IN CHAR)
       RETURN    BOOLEAN
IS
     m_total     NUMBER   :=  0;
     m_digito    NUMBER   :=  0;
BEGIN
     FOR i IN 1..9 LOOP
         m_total := m_total + substr(p_cpf,i,1) * (11 - i);
     END LOOP;

     m_digito := 11 - mod(m_total,11);

     IF m_digito > 9 THEN
        m_digito := 0;
     END IF;

     IF m_digito != substr(p_cpf,10,1) THEN
        RETURN FALSE;
     END IF;

     m_digito := 0;
     m_total  := 0;

     FOR i IN 1..10 LOOP
         m_total := m_total + substr(p_cpf,i,1) * (12 - i);
     END LOOP;

     m_digito := 11 - mod(m_total,11);

     IF m_digito > 9 THEN
        m_digito := 0;
     END IF;

     IF m_digito != substr(p_cpf,11,1) THEN
        RETURN FALSE;
     END IF;

     RETURN TRUE;

end;

Answer 1

The Boolean return can even be recognized by PL/SQL but not by SQL. For this reason when trying to execute the query:

SELECT CPF_VALIDACAO(NUMR_CPF) FROM PESSOAS

You will get the mistake: ORA-00902: tipo de dados inválido. The only solution is to change the return of your function to varchar or number.

Example:

CREATE OR REPLACE FUNCTION cpf_validacao(p_cpf IN CHAR) RETURN NUMBER IS
  m_total  NUMBER := 0;
  m_digito NUMBER := 0;
BEGIN
  FOR i IN 1 .. 9 LOOP
    m_total := m_total + substr(p_cpf, i, 1) * (11 - i);
  END LOOP;

  m_digito := 11 - MOD(m_total, 11);

  IF m_digito > 9 THEN
    m_digito := 0;
  END IF;

  IF m_digito != substr(p_cpf, 10, 1) THEN
    RETURN 0;
  END IF;

  m_digito := 0;
  m_total  := 0;

  FOR i IN 1 .. 10 LOOP
    m_total := m_total + substr(p_cpf, i, 1) * (12 - i);
  END LOOP;

  m_digito := 11 - MOD(m_total, 11);

  IF m_digito > 9 THEN
    m_digito := 0;
  END IF;

  IF m_digito != substr(p_cpf, 11, 1) THEN
    RETURN 0;
  END IF;

  RETURN 1;
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN 0;
END;

Then you test like this:

SELECT DECODE(CPF_VALIDACAO(NUMR_CPF),1,'VALIDO','INVALIDO') FROM PESSOAS

Or

SELECT CASE WHEN CPF_VALIDACAO(NUMR_CPF) = 1 THEN 'VALIDO' ELSE 'INVALIDO' END FROM PESSOAS