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

i have spent hours tracking down source of bug , stumped. managed determine below simple select statement problem (comment , not error.. yes, it's cause). n defined number, tried integer grins.

n integer;      n := 1; select count(*) n  category (       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)))       )     , (settings_setting_id = sett_id) , (category_id <> cat_id); 

when code executed, ora-01401: inserted value large column. "insert" n value row count. actual value (used debugger) 0.

i don't understand how causing problem. i've seen select count(*) x code snippet in examples. procedure runs fine statement commented out. time 'n' used in next step raise , exception if it's > 0. i've literally commented out entire stored procedure, leaving statement, , causes error.

my research online indicates count(*) returns integer.

the category table has 50 rows in it.

what missing?

this 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 other stuff around code snippet sent created new stored procedure assigned values passed parameters (the variables set in debugger). still ora-01401 on select count(*) n line. issues goes away when comment out clause.

create or replace procedure procedure1     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(*) n      category          (         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)))     )       , (settings_setting_id = sett_id) , (category_id <> cat_id)      ; end; 

try this:

declare     n number := 1; begin     select count(*) n      category         (         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)))     )       , (settings_setting_id = sett_id) , (category_id <> cat_id); end;