Re: Oracle 8.1.5 Bulk insert error

In article <2c9a9a67.94ce2638_at_usw-ex0105-035.remarq.com>,   justvrk <vrkulkarniNOvrSPAM_at_email.com.invalid> wrote:
> 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
>
>

I believe the problem is with the 'forall' statement; modifying the script to recode the 'forall' implied loop:

declare
type t$d is table of d.d%type
index by binary_integer;
lt$d t$d;
nt$d t$d;
begin
for i in 1..10 loop
  lt$d(i) := sysdate+i;
end loop;
for x in 1..lt$d.count loop
 insert into d
 values(lt$d(x));
end loop;
lt$d := nt$d;
end;
/

This script inserts the data as desired:

SQL> select *
  2 from d
  3 /

D

21-JUN-00
22-JUN-00
23-JUN-00
24-JUN-00
25-JUN-00
26-JUN-00
27-JUN-00
28-JUN-00
29-JUN-00
30-JUN-00

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.