Validation of CNPJ in PL/SQL Oracle

Question

I wonder if anyone knows or knows a CNPJ validator in PL/SQL Oracle.

Needed the user to put the value in a "dbms_output.put_line" and call a validator. I already got a validator that I will put down but unfortunately I can’t find this solution where the user puts the CNPJ and then is called the validator.

CREATE OR REPLACE FUNCTION CNPJ
  (p_cgc     IN CHAR)
   RETURN    BOOLEAN
IS
   m_total     NUMBER   :=  0;
   m_digito    NUMBER   :=  0;
BEGIN
 FOR i IN 1..4 LOOP
     m_total := m_total + substr(p_cgc,i,1) * (6 - i);
 END LOOP;

 FOR i IN 5..12 LOOP
     m_total := m_total + substr(p_cgc,i,1) * (14 - 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_cgc,13,1) THEN
    RETURN FALSE;
 END IF;

 m_digito := 0;
 m_total  := 0;

 FOR i IN 1..5 LOOP
     m_total := m_total + substr(p_cgc,i,1) * (7 - i);
 END LOOP;

 FOR i IN 6..13 LOOP
     m_total := m_total + substr(p_cgc,i,1) * (15 - 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_cgc,14,1) THEN
    RETURN FALSE;
 END IF;

 RETURN TRUE;
end;

Answer 1

All you have to do is create a Trigger for this, when there is a Insert or a update oracle itself validates whether the function returns true or false.

CREATE OR REPLACE TRIGGER TR_VALIDAR_CNPJ
AFTER INSERT OR UPDATE ON [OWNER].[TABELA]

BEGIN
   IF [NOME_FUNCAO] THEN
           -- CNPJ VALIDO, SÓ DAR CONTINUIDADE NO PROCESSO
       ELSE
           -- CNPJ ESTÁ INVALIDO, ENTÃO APARECE A MENSAGEM DE ERRO!
           RAISE_APPLICATION_ERROR(-20001, 'CNPJ inválido');
   END IF;

END;

Since I don’t know the name of the Owner, table and the name of its function, then I can’t make it 100% correct, but the way to work is this.