CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
Kiran Gharal
Kiran Gharal

Reputation: 175

Why does code throw java.sql.SQLException: ORA-01438?

I'm inserting data in table through this statement:

insert into CATEGORY_MASTER (
  CAT_MAS_ID, 
  DESCRIPTION, ORG_ID, STATUS, MODIFY_EMPID, LANGUAGE_ID, LG_IP_MAC)  
values ( 
  ( SELECT COALESCE(MAX(ct.cat_mas_id), 0)+1 
    FROM category_master ct),
  'fff', 2, 'A', 52,1,'SYSTEM/127.0.0.1/NOTDEFINE')

The target table has this trigger:

create or replace trigger trg_aft_i_u_category_master
  after insert OR UPDATE of cat_mas_id,status on category_master FOR EACH ROW
DECLARE
  CURSOR CSTYPE IS
    SELECT CST.SUB_TYPE_ID,CST.TYPE_ID,CST.ORG_ID,CST.STATUS
    FROM COMPLAINT_SUB_TYPE CST
    WHERE CST.ORG_ID=:NEW.ORG_ID AND CST.STATUS='A';
  V_CSTYPE CSTYPE%ROWTYPE;
BEGIN
  IF CSTYPE%ISOPEN THEN
    CLOSE CSTYPE;
  END IF;
  OPEN CSTYPE;
  LOOP
    FETCH CSTYPE INTO V_CSTYPE;
    EXIT WHEN CSTYPE%NOTFOUND;
    if INSERTING then
      /******** Suspect issue here  *******/
      INSERT INTO CATEGORY_DETAILS(
          CAT_DTL_ID, CAT_MAS_ID, TYPE_ID ,SUB_TYPE_ID,
          ORG_ID,MAP_STATUS,MODIFY_EMPID,LANGUAGE_ID,LG_IP_MAC)
      VALUES (SEQ_CATEGORY_DETAILS.NEXTVAL,:NEW.CAT_MAS_ID, 
          V_CSTYPE.TYPE_ID,V_CSTYPE.SUB_TYPE_ID,:NEW.ORG_ID,'U',
          :NEW.MODIFY_EMPID,:NEW.LANGUAGE_ID,:NEW.LG_IP_MAC);
      /************************************/
    end if;
    if UPDATING then
      if :new.status = 'I' then
        UPDATE CATEGORY_DETAILS CD 
        SET CD.MAP_STATUS= 'U' 
        WHERE CD.CAT_MAS_ID=:NEW.CAT_MAS_ID AND CD.ORG_ID=:NEW.ORG_ID;
      end if;
    end if;
  END LOOP;
  CLOSE CSTYPE;
end trg_aft_i_u_category_master;

Upvotes: 0

Views: 1649

Answers (1)

APC
APC

Reputation: 146349

The explanantion for ORA-01438 is:

"value larger than specified precision allowed for this column"

So one of your tables (not necessarily MASTER_CATEGORY) has a number column defined with significant digits, and your code is trying to insert a number which is too large.

Given this table ...

SQL> create table t42 (col1 number(5,2));

Table created.
SQL>

... we can insert a value which fits the declaration:

SQL> insert into t42 values (123.45);

1 row created.

SQL>

... the database rounds up trailing decimals:

SQL> insert into t42 values (12.345);

1 row created.

SQL>

... and the database rejects the value when the leading element is too large: 

SQL> insert into t42 values (1234.5);
insert into t42 values (1234.5)
                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL>

This now becomes an issue for you. You need to describe your tables to see which columns are defined as precise numbers, that is like NUMBER(3) or NUMBER(7,2). Then check the data you are using to estabish which numeric value is too big. Standard debugging techniques will help.

Upvotes: 1

Related Questions