CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Chaddeus
Chaddeus

Reputation: 13366

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

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?

Upvotes: 1

Views: 8159

Answers (1)

APC
APC

Reputation: 146239

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.

Upvotes: 3

Related Questions