Re: Bulk Collect Problem

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Wed, 26 Jan 2000 01:09:13 +0200
Message-ID: <388E2D19.8C2CFCBD@0800-einwahl.de>

Strange enough, this problem does not occur when using cursor variables. This one works:

set echo on
set feedback on
set serveroutput on 1000000

spool bulk_repeat_2.sql.lst

drop table test;

create table test (n number);

drop procedure testproc;

create procedure testproc is

	type rc is record (n number);
	type cc is ref cursor return rc;
	c cc;
	type t is table of number index by binary_integer;
	r t;
begin
	r.delete;
	open c for select n from test;
	fetch c bulk collect into r;
	close c;

end testproc;
/
show errors

execute testproc

execute testproc

spool off

Please try and confirm/deny.

Martin

Jonathan Lewis wrote:
>
> We knew what you meant. It also causes a
> problem with user-defined types anyway.
>
> The process goes into a spin attempting
> to BIND - (this can be seen from doing
> alter session set events '10046 trace name context forever, level 4';
>
> I haven't found a reasonable workaround yet
> and it doesn't appear to be on the 8.1.6
> bug fix list.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Martin Haltmayer wrote in message <388216E1.920F5015_at_0800-einwahl.de>...
> >Oops, sorry, wrong example:
> >
> >drop table test;
> >
> >create table test (n number);
> >
> >create or replace
> >procedure testproc is
> > cursor c is select n from test ;
> > type t is table of number index by binary_integer;
> > r t;
> >begin
> > r.delete;
> > open c;
> > fetch c bulk collect into r;
> > close c;
> >end testproc;
> >/
> >
Received on Tue Jan 25 2000 - 17:09:13 CST