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:
www.peakretrieval.com/plsql/Chapter6/varray_dml1.sql
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?
3 comments:
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
IS TABLE OF VARCHAR2(30 CHAR);
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?
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. " ?
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.
Post a Comment