0
I am creating a precedent to validate whether the field discount (%) receives a percentage less than zero (negative value) or greater than 100%.
But when executing appears an error that I am not able to solve. I searched several places and each speaks a different reason and even following I could not solve.
Could someone help me?
The error is as follows:
PLS-00306: Wrong number or types of Arguments in call to 'MV_VALIDA_DESCONTO_NEGATIVO'
CREATE OR REPLACE PROCEDURE TESTE."MV_VALIDA_DESCONTO_NEGATIVO" (
P_NUNOTA INT,
P_SUCESSO OUT VARCHAR2,
P_MENSAGEM OUT VARCHAR2
) AS
P_PERCDESC FLOAT ;
BEGIN
SELECT ITE.PERCDESC INTO P_PERCDESC FROM TGFITE ITE
WHERE ITE.NUNOTA = P_NUNOTA;
/* VERIFICA SE O CAMPO PERCENTUAL DE DESCONTO RECEBE VALOR NEGATIVO */
IF (P_PERCDESC < 0) THEN
P_SUCESSO := 'N';
P_MENSAGEM := ' PERCENTUAL NÃO PODE SER NEGATIVO!';
/* VERIFICA SE O CAMPO PERCENTUAL DE DESCONTO RECEBE MAIOR DO QUE 100% */
ELSIF (P_PERCDESC > 100) THEN
P_SUCESSO := 'N';
P_MENSAGEM := 'DESCONTO ULRAPASSOU LIMITE DE 100%';
ELSE
P_SUCESSO := 'S';
END IF;
P_SUCESSO := 'S';
END ;
It would be simpler to check Constraint https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/create-table-check-constraints.html
– Motta
Thanks for the tip. I’ll take a look at this documentation.
– psneves
Good morning, could you tell me the error in this check to bring me the error : SQL Error [2293] [23000]: ORA-02293: cannot validate (TESTE.CK_PERCDESC) - check Constraint violated
ALTER TABLE TGFITE
ADD CONSTRAINT CK_PERCDESC
CHECK ( PERCDESC BETWEEN 0 AND 100);
– psneves
Precisely you have PERCDESC that does not obey the rule , do ALTER TABLE TGFITE ADD CONSTRAINT CK_PERCDESC CHECK ( PERCDESC BETWEEN 0 AND 100) ENABLE NOVALIDATE; that Oracle will accept the Constraint but will only give error in some update , the best would be "hit" the basis if possible.
– Motta
I get it. So, the problem is that the software is third-party and we started the company and she said that it’s normal for it to be there and so it won’t be adjusted. So I tried to make the process up or that method you instructed.
– psneves
The Constraint worked out the way you guided me, Motta, thank you very much. I tested in the Insert of an item and as it comes with the value 0,00 then accuses the guitar for an update in the field.
– psneves