Subject arithmetic exception, numeric overflow, or string truncation
Author G G
Post date 2013-12-20T07:02:28Z
i have a table defined as:
create table organisation 
(
  orgid          bigint not null,
  title            varchar(30) not null,
  levels      smallint not null,
  parent      bigint ,
  inuse          char(1) default '1',
 constraint pk_organisation primary key (orgid)
);
alter table organisation add constraint fk_parent2orgid 
  foreign key (parent) references organisation
  (orgid) 
  on delete no action
  on update no action
 
and the stored procedure to for inserting data as:
 
create procedure insertorg (
  in_title varchar(30), 
  in_level smallint, 
  in_parent bigint)
as
declare variable insertsql varchar(90) = 'insert into organisation (title, 
        levels, parent) values (:ptitle, :plevel, :pparent)';
begin  
        if ((in_level =1 and in_parent is null) 
            or (in_level >1 and in_parent is not null) ) then  
       execute statement (insertsql) (ptitle := in_title, plevel := in_level, 
               pparent := in_parent );
end ^
 
when i try to use the procedure to insert a new record in the table i get the following error message:
  "arithmetic exception, numeric overflow, or string truncation string right truncation  at procedure 'insertorg' line: 3, col: 1".

however if i issue the insert command directly eg: insert into organisation (title, 
        levels, parent) values ('main dept',1,null);  this works fine.  (There is a before inset trigger on the table that takes care of issuing a value for the primary key).

What in the procedure is causing this error?  I've tried the procedure without the if then statement and still receive the same error.