Oracle 1Z0-183: Working with Composite Data Types

Records

TYPE t_rec IS RECORD (field datatype, ...) groups fields of differing types. %ROWTYPE produces a record matching a table row or, with cursor%ROWTYPE, a cursor's select list. Whole-record assignment works only when the structures match; you cannot assign a row record from table A to one from table B unless the field lists are compatible.

The three collection types

• Associative array (INDEX BY PLS_INTEGER or INDEX BY VARCHAR2): PL/SQL only, cannot be a table column, needs no constructor.
• Nested table: unbounded, can be a column, must be created with a constructor.
• VARRAY: bounded, can be a column; EXTEND beyond the declared limit raises ORA-06532.

Calling EXTEND on a collection left as NULL raises ORA-06531 - always initialise with type_name() first.

Collection methods

COUNT, FIRST, LAST, PRIOR(n), NEXT(n), EXISTS(n), EXTEND, TRIM, DELETE. DELETE with no argument empties the collection; DELETE(n) leaves a hole, so EXISTS(n) becomes false while COUNT shrinks and the index range still spans the gap. FIRST/LAST return NULL on an empty collection.

Rule of thumb: after any DELETE(n), FOR i IN 1..coll.COUNT is wrong. Iterate sparsely instead: i := coll.FIRST; WHILE i IS NOT NULL LOOP ... i := coll.NEXT(i); END LOOP;