Databases Reference
In-Depth Information
Beginning with Oracle Database 10
g
, automatic array fetching is
applied by default, with a limit of 100 rows for each loop iteration.
Find out more details here:
http://www.oracle.com/technetwork/
database/focus-areas/performance/designing-
applications-for-performa-131870.pdf
Also, refer to the following link for more information on this:
http://www.oracle.com/technetwork/issue-
archive/2011/11-may/o31asktom-354139.html
The
FORALL
statements allow us to use array processing for
INSERT
operations also. Despite
the appearance suggesting an iterative way of executing the statements inside the
FORALL
loop,
the operation is not really a loop, and it's executed once for all the values in the arrays used.
There is no automatic application of
FORALL
for insert-loop statements.
See also
F
See the
Introducing array and bulk operations
recipe in
Chapter 4
,
Optimizing SQL Code
Passing values with NOCOPY (or not)
In programming languages, we can pass parameters by reference and by value to a
function. In this recipe, we will see how to make out this difference in PL/SQL functions
and procedures.
How to do it...
The following steps will demonstrate passing parameters to functions:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
2.
Create the type
TAB_NUMBERS
, which is a table of numbers:
CREATE OR REPLACE TYPE sh.TAB_NUMBERS AS TABLE OF NUMBER;