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


	There are a number of grievances I’ve had with PL/SQL. These include an incomplete UTL_FILE package (also fixed in 9i), the inability to “introspect” to see what your name is and what line you died on, Oracle’s annoying habit of stripping comments and comment headers from triggers and views, and then the list of items in this slide. The first two items on this slide were fixed in 8i. The remainder were fixed in 9.0.1 and 9.2.

	Cannot store a list of values related to an individual row. Alternative: create a separate attributive table to hold the row's list. These "temporary" child tables clutter the schema, add processing and storage overhead, and make code more complex.

	Cannot use a collection in SQL statements. Alternative: create, populate and join to a temporary table.

	Cannot access list values using character-based keys. Alternative: create another kludge table to store the key:value pairs, or roll your own hash tables using a packaged PL/SQL table of record, hash algorithms and functions. Do-able, but not fun.

	Cannot insert or update a table using a record. Alternative: None. Break out a record into its individual columns for insert/update. This effectively eliminated the benefits of abstraction and de-coupling that records were meant to provide.

	NDS implementation didn’t support some of the nifty new features. Since NDS is where the real power is at, that was frustrating.

	Could not model an array in more than one dimension. I haven’t really had a need for this since my C days, but I’m sure other Oracle programmers have really missed the ability.

	Could not do array-like, or bulk SQL operations on collections. Alternative: None. Limited to one-by-one row processing. Shifting from the PL/SQL to SQL engine as each row was processed was a real drag on performance.

	Cannot use awesome BULK ops to get data directly into collections of record. Alternative: break every column down into its own collection of scalar. That was really unpleasant working with any more than a three-column table.