Re: Oracle 8.1.5 Bulk insert error

From: Alex P. Zotov <alex_at_soft-review.kiev.ua>
Date: 2000/06/20
Message-ID: <AAeMtJv850T@soft-review.kiev.ua>#1/1

Ok. I have had the same problem too. As I think this is one of a few Oracle bulk binds bugs.
I've resolved this bug by storing date in PL/SQL - table as varchar. E.g:
...
> 2 type t$d is table of d.d%type
> 3 index by binary_integer;

>>
type t$d is table of char(10)
index by binary_integer;
..
> 7 for i in 1..10 loop
> 8 lt$d(i) := sysdate+i;
> 9 end loop;
> 10 forall x in 1..lt$d.count
> 11 insert into d
> 12 values(lt$d(x));

>>
for i in 1..10 loop
  lt$d(i) := to_char(sysdate+i, 'DD.MM.YYYY'); end loop;
forall x in 1..lt$d.count
  insert into d
  values(to_date(lt$d(x), 'DD.MM.YYYY')); ..
BTW.
I've found other bulk binds bug. If you declare cursor to use for bulk fetch into PL/SQL - table and use it twice - you'll get ora-03113 error or you server will halt!!!
1. ora-03113
create table test_table1(id number(10, 0)); declare
type tblTyp is table of test_table1.id%type; cursor tblCur is
select id from test_Table;
tbl tblTyp;
begin
open tblCur;
fetch tblCur bulk collect into tbl;
fetch tblCur bulk collect into tbl; -- ora-03113 end;
2. Server will halt
E.g. Attention: this example may halt your oracle server! Do not run it! create procedure test1 as
type tblTyp is table of test_table1.id%type; cursor tblCur is
select id from test_Table;
tbl tblTyp;
begin
open tblCur;
fetch tblCur bulk collect into tbl;
end;

begin
-- Call the procedure
test1;
test1;// Server will halt!!!
end;

I've resolved this bug by use select .. bulk collect into.. instead of fetch cursor.

May be exists other "bulk binds" bugs and pathes for them. Bye.
.
----- Original Message -----
From: "justvrk" <vrkulkarniNOvrSPAM_at_email.com.invalid> Newsgroups: comp.databases.oracle.server To: <netters_at_lucky.net>
Sent: Tuesday, June 20, 2000 8:54 AM
Subject: Oracle 8.1.5 Bulk insert error

> Hi:

> I am facing a strange problem with bulk insert. Here is the

> complete log:
>
> SQL> rem  create table d ( d date );
> SQL>
> SQL> truncate table d;
>
> Table truncated.
>
> SQL>
> SQL> declare

> 2 type t$d is table of d.d%type
> 3 index by binary_integer;
> 4 lt$d t$d;
> 5 nt$d t$d;
> 6 begin
> 7 for i in 1..10 loop
> 8 lt$d(i) := sysdate+i;
> 9 end loop;
> 10 forall x in 1..lt$d.count
> 11 insert into d
> 12 values(lt$d(x));
> 13 lt$d := nt$d;
> 14 end;
> 15 /

>
> PL/SQL procedure successfully completed.
>
> SQL> select * from d

> 2 /

> ERROR:
> ORA-01858: a non-numeric character was found where
> a numeric was expected
>
>
> no rows selected
>
> SQL>
> SQL> select to_char(d,'DD-MM-YY') from d

> 2 /

>
> TO_CHAR(
> --------
> 00-00-00
> 00-00-00
> 00-00-00
> 00-00-00
> 00-00-00
> 00-00-00
> 00-00-00
> 00-00-00
> 00-00-00
> 00-00-00
>
> 10 rows selected.
>
> Anybody can figure out where I am going wrong ?
> BTW - I am working on
>
> Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> PL/SQL Release 8.1.5.0.0 - Production
> CORE Version 8.1.5.0.0 - Production
> TNS for 32-bit Windows: Version 8.1.5.0.0 - Production
> NLSRTL Version 3.4.0.0.0 - Production
>
> On a WindowsNT SP4. The database was created using dbassist and
> no extra patches/scripts run other than the one run by dbassist.
>
> Thanks and Regards,
> justvrk
>
> Got questions?  Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
>
>
>
>

Received on Tue Jun 20 2000 - 00:00:00 CDT