Re: 9i Bulk Insert Bug

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 Tue Apr 16 2002 - 18:16:42 CDT