CODEWITHSUNDEEP
oracle-databaseplsqlora-01438
user1700818
user1700818

Reputation: 71

ORA-01438: value larger than specified precision allowed for this column

Following is my code, I dont understand what I'm doing wrong. Any help will be greatly appreciated 

CREATE OR REPLACE
PROCEDURE COMP_LATE_FEE(LATE_APT_FINE IN NUMBER, LATE_GRG_FINE IN NUMBER)
AS
DIFF NUMBER;
TYPE MBCUR IS REF CURSOR RETURN MONTHLY_BILL%ROWTYPE;
MONBILL MBCUR;
MBREC MONTHLY_BILL%ROWTYPE;
BEGIN
--DIFF := FLOOR(SYSDATE - (TRUNC(SYSDATE,'MM')));
--DBMS_OUTPUT.PUT_LINE(DIFF);

OPEN MONBILL FOR
    -- checking the status of all last month's bills
    SELECT * FROM MONTHLY_BILL
    WHERE STATUS = 'PENDING' AND SYSDATE > ED_DT;
FETCH MONBILL INTO MBREC;
    -- adding the late fee amount for any bills that are past the due date
    -- due date = last day of the month
    DIFF := FLOOR(ABS(MBREC.ED_DT - (TRUNC(SYSDATE,'MM'))));
    UPDATE MONTHLY_BILL
    SET LATE_FEE = DIFF * LATE_APT_FINE
    WHERE BILL_NUM = MBREC.BILL_NUM;
    -- if a garage is rented by the resident then the respective additional fee is included
    IF (MBREC.GARAGE_RENT != 0) THEN
        UPDATE MONTHLY_BILL
        SET LATE_FEE = LATE_FEE + DIFF * LATE_GRG_FINE
        WHERE BILL_NUM = MBREC.BILL_NUM;
    END IF;

    COMMIT;
CLOSE MONBILL;
 END;
/

The procedure compiled without any err. But I get the following err when i call the proc

BEGIN
COMP_LATE_FEE(70,20);
END;
/

Error report:

ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at "LALLURI.COMP_LATE_FEE", line 19
ORA-06512: at line 2
01438. 00000 -  "value larger than specified precision allowed for this column"
*Cause:    When inserting or updating records, a numeric value was entered
           that exceeded the precision defined for the column.
*Action:   Enter a value that complies with the numeric column's precision,
           or use the MODIFY option with the ALTER TABLE command to expand
           the precision.

Upvotes: 2

Views: 9728

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

Assuming that the statement at line 19 is this

UPDATE MONTHLY_BILL
SET LATE_FEE = DIFF * LATE_APT_FINE
WHERE BILL_NUM = MBREC.BILL_NUM;

the problem would appear to be that the result of the computation diff * late_apt_fine is too large for the late_fee column in the monthly_bill table. We know that late_apt_fine is 70 based on the value of the parameter that was passed in. Since we don't know the value of the diff variable when there is an error and we don't know the definition of the late_fee column in monthly_bill, it's hard to know whether the problem is that the definition of late_fee needs to be changed or whether the computed value is larger than you expect and the algorithm needs to be changed.

Upvotes: 5

Related Questions