Oracle Procedure error: numeric or value error: number precision too large?

Question

Here's the procedure:

CREATE OR REPLACE PROCEDURE GetBestSellingMovieByTimeId(timeId IN NUMBER) IS
  movieName Movie.Name%type;
  saleValue Sales.SaleValue%type;
BEGIN
SELECT * INTO movieName, salevalue FROM (
  SELECT m.Name, SUM(s.SaleValue) AS TotalSales 
    FROM Sales s 
    INNER JOIN Movie m ON s.MovieId = m.MovieId 
    WHERE s.TimeId = timeId 
    GROUP BY m.Name ORDER BY TotalSales DESC
  ) WHERE ROWNUM = 1;
  dbms_output.put_line(movieName ||', ' || saleValue);
END;
/
exec GetBestSellingMovieByTimeId(2);

Here's the error:

Error starting at line 190 in command: exec GetBestSellingMovieByTimeId(2) 
Error report: 
 ORA-06502: PL/SQL:numeric or value error: number precision too large
 ORA-06512: at "CM420B17.GETBESTSELLINGMOVIEBYTIMEID", line 5 
 ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
 *Cause:    
 *Action:

TimeID is a NUMBER(2,0) FK on a Sales table. Passing the number 2 to the procedure shouldn't be out of range of the NUMBER(2,0) data type.

Why does this procedure think the IN parameter is too large?

Did you try running the query inside the function with the parameter on Developer or SQL*Plus? Looks to me like your saleValue variable is too short for the result. — tilley31
– tilley31, Commented Jul 23, 2013 at 2:16
The error is at line 5 --> SELECT * INTO movieName, salevalue FROM ... What is a type of saleValue Sales.SaleValue%type ? The procedure is trying to assign to this variable a result of SUM( xxx ), which is of type NUMERIC. — krokodilko
– krokodilko, Commented Jul 23, 2013 at 3:35
That was exactly the problem. The NUMBER field for SaleValue was too small to hold the SUM of the sale values. Thanks @tilly31 and kordirko, problem resolved. — Chaddeus
– Chaddeus, Commented Jul 23, 2013 at 3:36

APC · Accepted Answer · 2013-07-23 07:09:27Z

3

Your query selects SUM(s.SaleValue) into a variable saleValue defined as Sales.SaleValue%type.

Normally using the %type syntax is good practice, but only when it matches the assigned value. If Sales.SaleValue is defined with scale and precision it is quite likely that a SUM() of that column will blow the bounds of that definition. As appears to be the case here.

answered Jul 23, 2013 at 7:09

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle Procedure error: numeric or value error: number precision too large?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related