Oracle 8i and 9i PL/SQL Collections and Records


	A collection is nothing more than a list, an ordered group of elements, all of the same type – much like a single-dimension array in C or a one-column relational table. Each element in a collection has a unique index, or key, that determines its position in the collection. If you know the key, you are able to perform random access to the value, which is faster than iteration and even faster than SQL against the same information in an index-organized or cached table.
	Oracle provides three different collections: associative arrays, nested tables and varrays. “Associative” because the value is directly tied, or associated, with its numeric or character-based key. “Nested”, because as a column type, it allows you to embed, or nest, a table within a column of a row. “Varray”, short for “variable-size array”, is mystifying. The name and the Oracle docs[1] suggest that varrays are more flexible than the other collection types, when in fact, the opposite is true. I have yet to find that a varray was better suited to my problem than a nested table.
	[1] The PL/SQL User’s Guide is poorly worded. It says varrays “hold a fixed number of elements (although you can change the number of elements at runtime).” It also implies varrays are extensible: “To increase the size of a nested table or varray, use EXTEND.” It also showed a varray with a maxsize of 10, and then proceeded to explain that the uppder bound could be extended to “8, 9, 10, and so on.” Um, no. You will get ORA-06532 if you try to extend beyond 10, its maximum size.

	Locally means the collection type is only available inside PL/SQL, and unless it’s declared in a package specification, it’s only available to the current PL/SQL block. Globally means it is available for use within PL/SQL, SQL, object type definitions, and as a datatype for columns in database tables. If you want to perform SQL against your collection, it must be globally defined
	Code 1
	Code 2
	I did searches and determined that my existing uses of collections falls into one of two categories:
	1) Where I needed a temporary staging area for PKs or CORs and did not want to create a separate table for temporary data storage. This temporary data would then drive a further process to update records (kicking off a lengthy process based on the PK being fed to it) or delete. I was able revisit these recent and add bulk operations, speeding them up 60%.