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.
