Hej,

G G wrote:

> 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.

First i would try the procedure without the insertsql-stuff.

Marcus