PL/SQL Programming  «Prev  Next»
Lesson 1

Working with composite datatypes in Oracle PL/SQL

PL/SQL provides two composite datatypes: TABLE and RECORD. This module discusses the two composite datatypes which are TABLE and RECORD.
Module objectives
By the end of this module, you will know how to:
  1. Describe the structure of a PL/SQL table
  2. Define and populate a PL/SQL record
  3. Use %Type and %RowType attributes to manipulate data
In the next lesson, you will begin to learn the structure of a PL/SQL table.

Characteristics of PL/SQL Collection Types

Collection Type Number of Elements Subscript Type Dense or Sparse Where Created Can Be Object Type Attribute
Associative array (or index-by table) Unbounded String or integer Either Only in PL/SQL block No
Nested table Unbounded Integer Starts dense, can become sparse Either in PL/SQL block or at schema level Yes
Variable-size array (varray) Bounded Integer Always dense Either in PL/SQL block or at schema level Yes

PL/SQL data type categories

In a PL/SQL block, every constant, variable, and parameter has a data type. PL/SQL provides predefined data types and subtypes and lets you define your own PL/SQL subtypes.
Table below lists the categories of the predefined PL/SQL data types.

Predefined PL/SQL scalar data types

Data type category Description
Scalar Single values with no internal components
Composite Internal components that are either scalar or composite
Reference Pointers to other data items such as REF CURSORs