Re: Perplexing ORA-01461

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1071259254.443540_at_yasure...
> Haximus wrote:
> > "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> > news:1071252079.66421_at_yasure...
> >
> >>Haximus wrote:
> >>
> >>
> >>>I'm working on a Forms 9i application, the db used is Oracle9i
> >
> > Enterprise
> >
> >>>Edition 9.2.0.4.0 running on RedHat AS 3.0. I keep getting an
ORA-01461
> >>>error, which is defined as "can bind a LONG value only for insert into
a
> >>>LONG column." The perplexing part is none of the columns in the table
> >
> > being
> >
> >>>inserted are defined as LONG, nor are any of the variables I'm using.
> >
> > I've
> >
> >>>searched metalink and tried some of the suggested fixes but no joy.
> >>
> >>How about CLOB? BLOB? etc? What data types do you have?
> >
> >
> > This is the table definition itself:
> >
> > --- snip ---
> > create global temporary table tree (
> > id number not null primary key,
> > parent_id number,
> > rid rowid,
> > seq number,
> > selected char(1),
> > type char(1),
> > state number,
> > label varchar(100),
> > value number,
> > block varchar(30),
> > item varchar(30),
> > branch varchar(100),
> > options varchar(10),
> > expr1 varchar(1000),
> > expr2 varchar(1000),
> > expr3 varchar(1000),
> > check ( type in ('F','R') ),
> > check ( state between -1 and 1 )
> > )
> > on commit preserve rows;
> > --- unsnip ---
> >
> > The offending code looks something like this (edited for brevity);
> >
> > --- snip ---
> > procedure insert_node(pnode tree%rowtype) is
> > cnode tree%rowtype;
> > begin
> > -- assign some values to pnode
> > select seq.nextval into cnode.id;
> > cnode.parent_id := pnode.id;
> > cnode.type := 'F';
> > cnode.selected := 'N';
> > cnode.state := 0;
> > ...
> > insert into tree values (cnode); -- <<<< this is where the ORA-01461
> > occurs
> > ...
> > end;
> > --- unsnip ---

>

>

Hi, thanks for the reply.

I tried renaming the column identifiers that were reserved words but still same problem. I went back to metalink and did some more research. It turns out I encountered bug #3148927, which results in an ora-01461, caused on the client side (Forms in my case) when you try to insert a null into a varchar column after consecutive inserts of non-null values into the same column. The bug has not been patched or resolved yet, so it looks like I will need to "hack" a fix for now.

Regards Received on Wed Dec 17 2003 - 13:11:24 CST