This feature enables the selecting bulk of data in single network trip using
PL/SQL anonymous block. The OO4O
OraDynaset object
selects arrays of data
during SQL statement execution. but it involves overhead such as more network
roundtrips, creating cache files and creating more internal objects. If the
application does not want to use dynaset due to its overhead, then this feature is
useful for selecting arrays of data. The data to be selected can be bound either as
OraParamArray object or as OraCollection object. The following lines of code
explaining PL/SQL bulk collection features using OraCollection interface.
Set OraDatabase = OraSession.OpenDatabase("exampledb",
"scott/tiger", 0&)
'create a VARRAY type ENAMELIST in the database
OraDatabase.ExecuteSQL ("create type ENAMELIST as VARRAY(50)
OF VARCHAR2(20)")
'create a parameter for ENAMELIST VARRAY
OraDatabase.Parameters.Add "ENAMES", Null, ORAPARM_OUTPUT, 247,
"ENAMELIST"
'execute the statement to select all the enames from ename
'column of emp table
OraDatabase.ExecuteSQL ("BEGIN select ENAME bulk collect into
:ENAMES from emp; END;")
'here OraParameter object returns EnameList OraCollection
Set EnameList = OraDatabase.Parameters("ENAMES").Value
'display all the selected enames
FOR I = 1 to EnameList.Size
msgbox Enamelist(I)
NEXT I
The previous example explains how arrays of enames are selected with one
network round trip and less overload.
