JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 16: Collection Objects.


Bulk Collect (3rd Aug 2001)

Order By and UNION in Collections (21st May 2001)




Back to Book Index


Bulk Collect

I described the danger (p.425) of using the bulk collect syntax with pl/sql tables or object tables, advising you to use varray types with a user-defined exception to avoid the issue of runaway queries overloading your memory.

In Oracle 8.1.7 (and possibly extant but not documented in earlier versions) the bulk collect command comes complete with a throttle. You may now write code of the form:


	type n_table is table of number index by binary_integer;

	n1_tab n_table;
	n2_tab n_table;

	m_running_fetch		number := 0;

	cursor c1 is
	select n1,n2 from t1;

	open c1;
		fetch c1 
		bulk collect into n1_tab, n2_tab limit 100;

		forall i in 1..c1%rowcount - m_running_fetch
		insert into t2 values (n1_tab(i),n2_tab(i));

		m_running_fetch := c1%rowcount;
		exit when c1%notfound;

	end loop;

	close c1;

Note that there is an error in the example in the 8.1.7 PL/SQL manual on page 4-39. The construct exit when c1%notfound is used to jump out of the loop; however c1%notfound is set when the fetch returns fewer rows than the limit, so the exit takes place without processing the last few rows of the cursor.

I have also found, using increasing numbers of rows in the table t1 above, that there seems to be an implementation error that allows the PGA to grow, despite the apparent re-use of the same set of entries in the arrays n1_tab and n2_tab. In my test cases, this was only a relatively small increase in memory as the number of times through the loop increased. You may wish to experiment further before using this technique for large volumes of data.

(Acknowledgements: My thanks to Anthony Smith for pointing out the existence of this feature during his attendance at the July seminar).

Top of page


Order By and UNION in Collections:

I mentioned a bug with order by clauses (p.357) when using cast(multiset()) with inline views, and pointed out that this was supposed to be fixed in Oracle 8.1.6. This note is just to confirm that this bug definitely was fixed.

As a demonstration of this fix, the following code is a small sample that also demonstrates how you can use a union operator inside a cast(multiset()) without getting the error messages ' PLS-00605, UNION operators not supported by multiset.' and 'ORA-03001: unimplemented feature'. The trick (which is worth trying in all sorts of cases) is to hide the problem inside a view, in this case an in-line view.

drop type jpl_array;
drop type jpl_row;

create type jpl_row as object (object_type varchar2(18), object_name varchar2(128));

create type jpl_array as table of jpl_row;

select object_type, object_name 
table (					-- turn the table-type into a 'real' table
	cast(				-- cast it into the type
		multiset(		-- expected to return many rows
			select * from (	-- hide the UNION (ALL) in an in-line view
				select		-- select statement 1
				from user_objects
				union all
				select		-- select statement 2
				from user_objects
			order by 2,1
		) as jpl_array


Top of page




Top of page

Back to Book Index