CODEWITHSUNDEEP
oracle-databaseoracle9i
HC_Coder
HC_Coder

Reputation: 31

Oracle select count(*) into n generates ORA-01401 inserted value too large for column

I have spent hours tracking down the source of this bug and am stumped. I managed to determine that the below simple select statement was the problem (comment and not error.. so yes, it's the cause). n was initially defined as a number, I tried integer as well for grins.

n integer;

    n := 1;
select count(*) into n 
from category
where (
      upper(ltrim(rtrim(category_long_name))) = upper(ltrim(rtrim(cat_long_name)))
        or
      upper(ltrim(rtrim(category_short_name))) = upper(ltrim(rtrim(cat_short_name)))
        or
      upper(ltrim(rtrim(category_description))) = upper(ltrim(rtrim(cat_descr)))
      )  
  and (settings_setting_id = sett_id) and (category_id <> cat_id);

When this code is executed, I get ORA-01401: inserted value too large for column. So the only "insert" is into the n value for a row count. The actual value (used debugger) is 0.

I don't understand how this could be causing a problem. I've seen this select count(*) into x code snippet in examples. The procedure runs fine with this statement commented out. The only time 'n' is used is in the next step where I raise and exception if it's > 0. I've literally commented out the entire stored procedure, leaving only this statement, and it causes the error.

My research online indicates that count(*) returns an integer.

The category table has about 50 rows in it.

What am I missing?

This is category: "CATEGORY_ID" NUMBER(,0), "VERSION_VERSION_ID" NUMBER(,0), "SETTINGS_SETTING_ID" NUMBER(*,0), "CATEGORY_LONG_NAME" CHAR(256 BYTE), "CATEGORY_SHORT_NAME" CHAR(25 BYTE), "CATEGORY_DESCRIPTION" VARCHAR2(4000 BYTE), "CATEGORY_FORM_ID" CHAR(10 BYTE), "CATEGORY_FORM_SYNONYM" CHAR(256 BYTE), "CATEGORY_GUIDE_FOR_USE" VARCHAR2(4000 BYTE), "CATEGORY_COMMENTS" VARCHAR2(4000 BYTE), "CATEGORY_EFFECTIVE_DATE" DATE, "CATEGORY_UNTIL_DATE" DATE, "CATEGORY_CREATOR" CHAR(50 BYTE), "CATEGORY_ADMIN_STATUS" CHAR(25 BYTE), "CATEGORY_ADMIN_STATUS_DATE" DATE, "CATEGORY_REGISTR_STATUS" CHAR(25 BYTE), "CATEGORY_REGISTR_STATUS_DATE" DATE, "CATEGORY_STATUS" VARCHAR2(10 BYTE), "CATEGORY_STATUS_JUST" VARCHAR2(2000 BYTE), "CATEGORY_TYPE" NUMBER

There was some other stuff around the code snippet I sent so I created a new stored procedure where I assigned the values that would be passed as parameters (the variables I set in the debugger). I still get the ORA-01401 on the select count(*) into N line. The issues goes away when I comment out the where clause.

create or replace PROCEDURE PROCEDURE1 
IS 
  CATEGORY_NAME_EXISTS Exception;
  WRONG_ACTION_PARAM Exception;
  WRONG_PARAM_SET Exception;
  NO_JUSTIFICATION Exception;
  VERSION_PERSISTENT Exception;
  CANNOT_APPROVE Exception;
  VERSION_SETTING_NEEDED Exception;
  n number :=1;
  msg1 nvarchar2(2000);
  curr_status nvarchar2(10);
  curr_persistent number;
  curr_sett_status nvarchar2(10);
  update_with_hierarchy nvarchar2(3);
  sql_txt nvarchar2(1000);  
  err_num number;
  err_msg varchar2(200);
  CAT_LONG_NAME nvarchar2(1000) := 'Administrative';
  CAT_SHORT_NAME nvarchar2(1000) := 'Administrative';
  CAT_DESCR nvarchar2(1000) := 'Admin form';
  SETT_ID number := 2;
  CAT_ID number := 13;
  categORy_long_name nvarchar2(1000);
  categORy_shORt_name nvarchar2(1000);
  categORy_description nvarchar2(1000);
  settings_setting_id number;
  categORy_id number;
BEGIN

    SELECT COUNT(*) INTO n 
    FROM categORy

    WHERE
    (
        UPPER(LTRIM(RTRIM(categORy_long_name))) = UPPER(LTRIM(RTRIM(cat_long_name)))
        OR
        UPPER(LTRIM(RTRIM(categORy_shORt_name))) = UPPER(LTRIM(RTRIM(cat_shORt_name)))
        OR
        UPPER(LTRIM(RTRIM(categORy_description))) = UPPER(LTRIM(RTRIM(cat_descr)))
    )  
    AND (settings_setting_id = sett_id) and (categORy_id <> cat_id)

    ;
END;

Upvotes: 3

Views: 595

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

Try this:

DECLARE
    n number := 1;
BEGIN
    SELECT COUNT(*) INTO n 
    FROM categORy
    WHERE
    (
        UPPER(LTRIM(RTRIM(categORy_long_name))) = UPPER(LTRIM(RTRIM(cat_long_name)))
        OR
        UPPER(LTRIM(RTRIM(categORy_shORt_name))) = UPPER(LTRIM(RTRIM(cat_shORt_name)))
        OR
        UPPER(LTRIM(RTRIM(categORy_description))) = UPPER(LTRIM(RTRIM(cat_descr)))
    )  
    AND (settings_setting_id = sett_id) and (categORy_id <> cat_id);
END;

Upvotes: 0

Related Questions