Validate Cpf’s from a query

Asked

Viewed 311 times

0

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;
  • Which means "it doesn’t work"?

  • If possible change k the return of Function from Boolean to number (0 not valid , 1 valid), use Boolean in some frontends can give zebra.

1 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
  • 1

    Just one remark: the Boolean data type is not recognized by Oracle but is recognized by other DBMS.

  • Thank you for your reply.

  • It worked, but even invalid cpfs remain valid

  • @anonymo yes, but as he tagged, I didn’t even comment on.

  • @Carloshenrique you can mark an answer as accepted if it helped you. If there are invalid Cpfs returning as valid, it may be a logic problem.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.