0
Good afternoon people, I have the code below to validate Cpf and cnpj However, when trying to call the sql function, it does not execute because of the following error: [Code: 6552, SQL State: 65000] ORA-06552: PL/SQL: Statement Ignored ORA-06553: PLS-382: The expression is of the incorrect type
I am using this sql to make the query: SELECT DESC_NOME, NUMR_CPF, CASE WHEN VALIDA_CPF_CNPJ(NUMR_CPF) = 1 THEN 'VALIDO' ELSE 'INVALIDO' END AS CPF_VALIDACAO FROM PESSOAS
CREATE OR REPLACE FUNCTION "EPREV"."VALIDA_CPF_CNPJ"(V_CPF_CNPJ VARCHAR2) RETURN BOOLEAN IS
TYPE ARRAY_DV IS VARRAY(2) OF PLS_INTEGER;
V_ARRAY_DV ARRAY_DV := ARRAY_DV(0, 0);
CPF_DIGIT CONSTANT PLS_INTEGER := 11;
CNPJ_DIGIT CONSTANT PLS_INTEGER := 14;
IS_CPF BOOLEAN;
IS_CNPJ BOOLEAN;
V_CPF_NUMBER VARCHAR2(20);
TOTAL NUMBER := 0;
COEFICIENTE NUMBER := 0;
DV1 NUMBER := 0;
DV2 NUMBER := 0;
DIGITO NUMBER := 0;
J INTEGER;
I INTEGER;
BEGIN
IF V_CPF_CNPJ IS NULL THEN
RETURN FALSE;
END IF;
IF V_CPF_CNPJ = '11111111111' OR V_CPF_CNPJ = '22222222222' OR V_CPF_CNPJ = '33333333333' OR V_CPF_CNPJ = '44444444444' OR V_CPF_CNPJ = '55555555555'
OR V_CPF_CNPJ = '66666666666' OR V_CPF_CNPJ = '77777777777' OR V_CPF_CNPJ = '88888888888' OR V_CPF_CNPJ = '99999999999' OR V_CPF_CNPJ = '00000000000'
THEN
RETURN FALSE;
END IF;
V_CPF_NUMBER := REGEXP_REPLACE(V_CPF_CNPJ, '[^0-9]');
IS_CPF := (LENGTH(V_CPF_NUMBER) = CPF_DIGIT);
IS_CNPJ := (LENGTH(V_CPF_NUMBER) = CNPJ_DIGIT);
IF (IS_CPF OR IS_CNPJ) THEN
TOTAL := 0;
ELSE
RETURN FALSE;
END IF;
DV1 := TO_NUMBER(SUBSTR(V_CPF_NUMBER, LENGTH(V_CPF_NUMBER) - 1, 1));
DV2 := TO_NUMBER(SUBSTR(V_CPF_NUMBER, LENGTH(V_CPF_NUMBER), 1));
V_ARRAY_DV(1) := 0;
V_ARRAY_DV(2) := 0;
FOR J IN 1 .. 2
LOOP
TOTAL := 0;
COEFICIENTE := 2;
FOR I IN REVERSE 1 .. ((LENGTH(V_CPF_NUMBER) - 3) + J)
LOOP
DIGITO := TO_NUMBER(SUBSTR(V_CPF_NUMBER, I, 1));
TOTAL := TOTAL + (DIGITO * COEFICIENTE);
COEFICIENTE := COEFICIENTE + 1;
IF (COEFICIENTE > 9) AND IS_CNPJ THEN
COEFICIENTE := 2;
END IF;
END LOOP;
V_ARRAY_DV(J) := 11 - MOD(TOTAL, 11);
IF (V_ARRAY_DV(J) >= 10) THEN
V_ARRAY_DV(J) := 0;
END IF;
END LOOP;
RETURN(DV1 = V_ARRAY_DV(1)) AND(DV2 = V_ARRAY_DV(2));
END VALIDA_CPF_CNPJ;
According to the answer given to your other question (https://answall.com/questions/452644/validar-cpfs-de-uma-query) the problem is the
RETURN BOOLEAN
on the Oracle.– anonimo
just as @anonimo scored, you can use the Boolean type inside Function, but not in return, because you don’t try to return an int? (1 / 0 instead of true/false)
– Ricardo Pontual
I just don’t know how to change this last Return ai. always gives error
– Carlos Henrique