Re: 9i Bulk Insert Bug

From: David Fitzjarrell <oratune_at_msn.com>
Date: 17 Apr 2002 06:19:09 -0700
Message-ID: <32d39fb1.0204170519.76332bfc@posting.google.com>

Ditto for 9.0.1.2.1:

SQL> select *
  2 from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

--------------- ----------------

HOST_NAME

---

VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_

----------------- --------- ------- --- ---------- ------- -----------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST
---------- --- ----------------- ------------------ ---------
              1 dbtech
DAVIDF
9.0.1.2.1         08-APR-02 OPEN    NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL

SQL> drop table x;

Table dropped.

SQL> create table x (value number);

Table created.

SQL>
SQL> declare
  2
  3 type ttab_value is table of x.value%type index by   4 binary_integer;
  5 tab_value ttab_value;
  6
  7 begin
  8

  9          tab_value(1) := 1;
 10          tab_value(1) := null;
 11
 12          if tab_value(1) is null then
 13                  dbms_output.put_line('the value in index 1 is null');
 14          end if;
 15
 16          forall i in 1 .. 1
 17                  insert into x (
 18                          value
 19                  ) values (
 20                          tab_value(i)
 21                  );

 22
 23 end;
 24 /

PL/SQL procedure successfully completed.

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

TEST_RESULT

---

Everything is ok, no bug detected

SQL> select 'THIS IS A BUG!!! VALUE SHOULD BE NULL' as test_result from x   2 where value is not null;

no rows selected

SQL> "Sunil" <sunil_franklin_at_hotmail.com> wrote in message news:<cv7v8.30$tV6.192_at_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 - 08:19:09 CDT