Eureka! Collections are not arrays, they are tables!

I came from a land far, far away, where PICK was king and all was well. Ok, are you done laughing? Then, by all means continue.

Years later, I am in Oracle land.

I arrays in memory.

They are called Collections... there is a reason why they are not arrays.

In my other life it was

Dim Array1(x,y,z,..n)

Array1(x,y,z,..n) = Whatever
or
Whatever = Array1(x,y,z)

Not so in Oracle

In Oracle it is;
  Fixed or variable array (varray v. nested table)
  Single dimension (Array1(i))
  and there are actions that can and need to be done to make em work

1. Declare
2. Intialize
3. Load if the initialize didn't do that
4. Work with it

I needed a two dimensional array. To make a long story short.

1. Create a record type
2. Create a table type based on the record
3. Initialize the table

Initializing requires

1. Extending
2. Loading directly using the v_table_type.fieldname (The names given in the record type creation)

Now it is ready for use.

This was hard for me because the setup and loading is not intuitive coming from the rest of the world. Collections are all about a table view of the data. I think of them as a subset of true arrays.

All that said, and it wasn't much, I may have missed the bigger picture if I needed an array with more than two dimensions.

Finally, in PICK. The language was built from the ground up to denormalize data using true nested tables. I see a lot of examples around books, beers, and employees but they use a column in a hetrogenous way (unlike data stored as multiple columns in a single column). Trun non-fomral form (which could be a form of mormalization as the tuples could contain unique, though many to one data in a column. PICK also had the report and manipulation commands built into the language so there was no additional effort to 'explode' a nested record.

A short example to better illustrate my point, would be an invoice

There is header data and detail or child records. In RDBMs that would be two tables. In PICK you had that option, but you could also create a record like this

Invoice_Table
Header fields:
--------------------
Invoice_Number
Customer
Ship_to
Bill_to'
Total_amount
Due_Date
Terms

Detail fields: (each filed containes multiple lines that are part of the detail set, row separators shown as ^)
----------------------
Line_number:    1^2^3^4
Item_number    12345^ABC123^AJ-B45CET^ND1
Cost                 25.23^30.00^12.95^0
Quantity            1^1^1^1


And PICK could had a true data dictionary that was more Business Objects like.

Don't get me wrong. I like Oracle and it has many great features, but tgrue nested tables are not one of them. They could be if the language was fully extended to create the required environment for clean SQL access.