Using the PL/SQL Bulk Collect Feature

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 round-trips, 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.