Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 8.1.5 Bulk insert error

Re: Oracle 8.1.5 Bulk insert error

From: justvrk <justvrk_at_my-deja.com>
Date: 2000/06/27
Message-ID: <8j9775$fl0$1@nnrp1.deja.com>#1/1

Hi:
The forall date bug and some others got ironed out by applying the 8.1.5.1.0 patch.

HTH,
justvrk

In article <AAeMtJv850T_at_soft-review.kiev.ua>,   alex_at_soft-review.kiev.ua wrote:
> 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
> >
> >
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jun 27 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US