Problem with validation process

Asked

Viewed 29 times

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

  • Thanks for the tip. I’ll take a look at this documentation.

  • 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&#xA;ADD CONSTRAINT CK_PERCDESC&#xA;CHECK ( PERCDESC BETWEEN 0 AND 100);

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

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

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

Show 1 more comment
No answers

Browser other questions tagged

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