#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Goi‚nia,GO,Brazil
    Posts
    17
    Rep Power
    0

    Error in procedure


    I'm getting the following error on a procedure.

    Arithmetic overflow or division by zero has occurred.
    arithmetic exception, numeric overflow, or string truncation.

    I tried to find the error but I just couldn`t. Could anybody help me? thanks

    Code:
    CREATE OR ALTER PROCEDURE VLR_CLASSE(P_REF_ANOMES NUMERIC(04),P_CDG_ORGAO NUMERIC(03),  
          P_CLASSE VARCHAR(02))
    RETURNS(R_VALOR NUMERIC(13,2)) 
    
    AS
      
    BEGIN
       R_VALOR = 0;
    
    
       SELECT 
           C.VLR_CLASSE
         FROM 
           TBCLASSE C
         WHERE (C.CDG_ORGAO = :P_CDG_ORGAO)
           AND (RTRIM(C.CLASSE) = RTRIM(:P_CLASSE))
           AND (C.DTA_INI_CLASSE = (SELECT MAX(C2.DTA_INI_CLASSE) 
                                   FROM TBCLASSE C2 
                                   WHERE (CAST(CAST(EXTRACT(YEAR FROM C2.DTA_INI_CLASSE) || LPAD(EXTRACT(MONTH FROM C2.DTA_INI_CLASSE),2,'0') AS VARCHAR(10))AS NUMERIC(10)) <= :P_REF_ANOMES)
                                     AND C2.CDG_ORGAO = C.CDG_ORGAO 
                                     AND C2.CLASSE = C.CLASSE))
        INTO :R_VALOR; 
    
      IF (R_VALOR IS NOT NULL) THEN 
         BEGIN
          R_VALOR = COALESCE(R_VALOR,0);       
         END
    
      
       SUSPEND;
    END;
    Thank you, bjos
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    14
    Hard to tell what the problem is with only this little info...

    Can you run the SELECT statement by itself?
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Goi‚nia,GO,Brazil
    Posts
    17
    Rep Power
    0
    Only the "Select.." yeah I can. But outside the procedure. If I put it inside it again (and for it I have to add the "into..." part), taking out every other code of the procedure and try to execute the procedure again.. it gives me the same error
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    14
    And you're sure the result fits in variable "R_VALOR"?
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Goi‚nia,GO,Brazil
    Posts
    17
    Rep Power
    0
    VLR_CLASSE and R_VALOR are the same type! NUMERIC(13,2)

    What I`m thinking now is... my procedure is called VLR_CLASSE, and so is the table field I`m getting the value. Could that be a problem?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    14
    No, that can't be it.

    Care to send me a backup of your database so that I can take a closer look at it?

    What version of Firebird are you using?

    If you like to send me a copy, send it to m.tonies @ upscene.com
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Goi‚nia,GO,Brazil
    Posts
    17
    Rep Power
    0
    Just sent you a copy. Thank you very much.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    14
    Hi,

    What values do you use for the input parameters?

    I think it might be related to parameter "P_REF_ANOMES".

    Can you explain what value has to go into that parameter?
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Goi‚nia,GO,Brazil
    Posts
    17
    Rep Power
    0
    OOPS
    i changed it from NUMERIC(04) to NUMERIC(06) (because it has to fit the value 200312, argh, what an asshole i am). Anyways.. it keeps giving the same error. Perhaps because both fit into the SMALLINT description...
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    14
    Hi,

    It works fine here, with a NUMERIC(6) for that particular parameter.

    Make sure you disconnect all connections after modifying the procedure, re-connect and try again. Sometimes, Firebird keeps a procedure in it's cache if anyone else is connected and has run the procedure.
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Goi‚nia,GO,Brazil
    Posts
    17
    Rep Power
    0
    :S I'll kill myself

    Thank you upscene
    sorry :S

IMN logo majestic logo threadwatch logo seochat tools logo