Friday, October 07, 2005

Prog rock

Anyone who remembers The Ramones will have happy memories of their toetappingly monolithic slabs of pure punk pop. After a couple of albums of two-chord magic, however, there were those who said it was starting to sound samey, and I believe it was Joey Ramone's dad who commented in an interview that perhaps the boys might want to "complicate it up a bit".

It seems the authors of Oracle Database 10g PL/SQL Programming (Oracle Press, 2004) chose to follow similar advice in their chapter on working with 8i, er, I mean 10g collections. Anyone looking for a funkier way to DELETE FROM addresses WHERE address_id = 11, for example, need look no further:

And while we're at it,

SELECT column_value
FROM   THE (SELECT CAST(emp_address AS varray_nested_table )
            FROM   emp
            WHERE  empno = 11);

(love that type name btw) could also be written as

SELECT column_value
FROM   emp, TABLE(emp_address)
WHERE  empno = 11;

but where's the fun in that?


OraWTFGuy said...

Well there's no point trying the delete if there's nothing to delete ;o)

I particularly like the following:-

-- Create a PL/SQL table data type.
CREATE OR REPLACE TYPE varray_nested_table

Now look here. This is a SQL command, creating a database object, namely a NESTED TABLE type. Nothing else. A nested table type.

The PL/SQL table mentioned in the comment does not come into the equation here. It ain't a PL/SQL table. You might choose to declare a PL/SQL variable of this nested table type, but a PL/SQL table it will not be.

As for VARRAY, implied by the new type's name: this does not even figure in a thousand years. A VARRAY is not a NESTED TABLE and vice versa. They are both COLLECTION types, true, but that's where it ends.

So come on fellas, what's it going to be?

Thai Rices said...

Is there a hint further on that says:

"When inserting records into a table is a requirement because of a 'mandatory parent key constraint' be sure to implicitly type cast the key value. " ?

William Robertson said...

Er, no.

But just for fun, check out create_nestedtable1.sql and see if you can see whether they are talking about a varray or a nested table. Damn, those things are easy to mix up.