JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 19: PL/SQL.


BULK COLLECT (21st Oct 2001)

NOCOPY (13th Mar 2001)





Back to Book Index


Bulk Collect (21st Oct 2001)

When using the BULK COLLECT feature to do array processing, I mentioned (p. 425) that there was no way of limiting the volume of data returned, other than my suggestion of using bulk collect into a varray with the deliberate intention of raising an exception if the user tried to acquire too much data. However, during one of my seminars, Anthony Smith (Deutsche Bank AG) pointed out to me the existence of a LIMIT option, a fact also emailed to me by Robin Koumis who mentioned that 8.1.6 onwards allows this LIMIT keyword to go with bulk collect so that you can control the amount of data returned. For example:

	type vc_tab is table of varchar2(40) index by binary_integer;
	m_tab	vc_tab;

	cursor c1 is 
	select object_name
	from all_objects
	where rownum <= 33;

	open c1;
		fetch c1 
		bulk collect 
		into m_tab limit 10;

		for i in 1..m_tab.count loop
		end loop;

		exit when c1%notfound;

	end loop;
	close c1;

Top of page




During a recent site visit, I came across some heavy-duty PL/SQL which spent a lot of its time passing large 'IN OUT' parameters between PL/SQL procedures, for example varrays, complex records, and pl/sql tables. The entire package was consuming lots of CPU, and it seemed likely that this was in part due to the work Oracle was doing in copying these parameters back and forth, as Oracle traditionally passes parameters by value, not by reference. (Unfortunately this was Oracle 8.0, so I couldn't call on the dbms_profiler package to profile the PL/SQL and tell me which bits were costing the most).

It was at this point that I discovered that I had forgotten to mention the NOCOPY compiler hint introduced in Oracle 8.1. to reduce the cost of passing large pl/sql variables between procedures. A sample of the syntax is:

	procedure analyse_list(
		io_list 	in out 	nocopy my_list_type
	) is

There are traps, of course. If you pass a parameter by reference in this way, then the called procedure is modifying the actual value known to the calling program; if the procedure raises an exception, then you could end up falling back to the main program with the data partly modified. This is a circumstance which cannot occur when passing data by value - the value is copied to the procedure, and only copied back if the procedure terminates successfully. YOu also have to be careful if you pass global variables with the NOCOPY hint as this may result in a procedure being able to address the same actual variable using two different names (the global name, and the parameter name) with doubtful results. Be very careful of side-effects when you start to use the NOCOPY hint.

If you are using IN parameters, the NOCOPY hint is redundant; pure IN parameters are always passed by reference; the NOCOPY hint applies only to OUT and IN OUT parameters.

Top of page





Top of page

Back to Book Index