Re: 9i Bulk Insert Bug

From: Sunil <sunil_franklin_at_hotmail.com>
Date: Wed, 17 Apr 2002 10:40:15 +0530
Message-ID: <cv7v8.30$tV6.192@news.oracle.com>

Looks like it is fixed !

SQL> select 'Everything is ok, no bug detected' as test_result from x where value is null;

TEST_RESULT

---

Everything is ok, no bug detected

SQL> select * from v$version;

BANNER

---

Oracle9i Release 9.0.1.3.0 - Production
PL/SQL Release 9.0.1.3.0 - Production
CORE 9.0.1.2.0 Production

TNS for Solaris: Version 9.0.1.3.0 - Production NLSRTL Version 9.0.1.3.0 - Production

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:uh2v8.40220$VQ2.23999124_at_twister.socal.rr.com...
> It's worse than I thought. Bulk inserts can't handle nulls at all. My
> guess is that it's inserting whatever it finds in memory even if it
> hasn't been initialized. This test script gives 'ERROR: ORA-01801: date
> format is too long for internal buffer'. Note: if my guess is right,
> this script may occasionally succeed if the uninitialized memory value
> just happens to looks like a date...
>
>
> drop table x;
> create table x (value date);
>
> declare
>
> type ttab_value is table of x.value%type index by
> binary_integer;
> tab_value ttab_value;
>
> begin
>
> tab_value(1) := null;
>
> if tab_value(1) is null then
> dbms_output.put_line('the value in index 1 is null!');
> end if;
>
> forall i in 1 .. 1
> insert into x (
> value
> ) values (
> tab_value(i)
> );
>
> end;
> /
>
> select value from x;
>
>
> Richard Kuhler wrote:
> >
> > Looks like bulk inserts are screwed up in 9i if you change a table
> > element to null. Can someone please confirm this bug with the test
> > script below? Anybody with a version greater than 9.0.1.0.0 see it
> > fixed? Note, this works fine in 8.1.7.2.0.
> >
> > drop table x;
> > create table x (value number);
> >
> > declare
> >
> > type ttab_value is table of x.value%type index by
> > binary_integer;
> > tab_value ttab_value;
> >
> > begin
> >
> > tab_value(1) := 1;
> > tab_value(1) := null;
> >
> > if tab_value(1) is null then
> > dbms_output.put_line('the value in index 1 is null');
> > end if;
> >
> > forall i in 1 .. 1
> > insert into x (
> > value
> > ) values (
> > tab_value(i)
> > );
> >
> > end;
> > /
> >
> > select 'Everything is ok, no bug detected' as test_result from x where
> > value is null;
> > select 'THIS IS A BUG!!! VALUE SHOULD BE NULL' as test_result from x
> > where value is not null;
>
Received on Wed Apr 17 2002 - 00:10:15 CDT