Addenda
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:
declare 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; begin open c1; loop fetch c1 bulk collect into m_tab limit 10; for i in 1..m_tab.count loop dbms_output.put_line(m_tab(i)); end loop; exit when c1%notfound; end loop; close c1; end; /
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 begin ... end;
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.