10.4.1 Defining the Table TYPE

The TYPE statement for a PL/SQL table has the following format:

TYPE <table_type_name> IS TABLE OF <datatype> [ NOT NULL ]
   INDEX BY BINARY_INTEGER;

where <table_type_name> is the name of the table structure you are creating and <datatype> is the datatype of the single column in the table. You can optionally specify that the table be NOT NULL, meaning that every row in the table that has been created must have a value.

You must always specify INDEX BY BINARY_INTEGER at the end of the TYPE...TABLE statement, even though it is the only type of index you can have currently in a PL/SQL table.

PL/SQL uses BINARY_INTEGER indexes because they allow for the fastest retrieval of data. (In this case, the primary key is already in the internal binary format, so it does not have to be converted before it can be used by the runtime environment.)

The rules for the table type name are the same as for any identifier in PL/SQL: the name can be up to 30 characters in length, it must start with a letter, and it can include some special characters such as underscore ( _ ) and dollar sign ( $ ).

The datatype of the table type's column can be any of the following:

Scalar datatype

Any PL/SQL-supported scalar datatype, such as VARCHAR2, POSITIVE, DATE, or BOOLEAN.

Anchored datatype

A datatype inferred from a column, previously defined variable, or cursor expression using the %TYPE attribute.

Here are some examples of table type declarations:

TYPE company_keys_tabtype IS TABLE OF company.company_id%TYPE NOT NULL
   INDEX BY BINARY_INTEGER;

TYPE reports_requested_tabtype IS TABLE OF VARCHAR2 (100)
   INDEX BY BINARY_INTEGER;

NOTE: Prior to PL/SQL Release 2.3, you may not use composite datatypes declaring a PL/SQL table's column. With Release 2.3, you can create PL/SQL tables of records.

10.4.2 Declaring the PL/SQL Table

Once you have created your table type, you can reference that table type to declare the actual table. The general format for a table declaration is:

<table_name> <table_type>;

where <table_name> is the name of the table and <table_type> is the name of a previously declared table type. In the following example I create a general table type for primary keys in PACKAGE and then use that table type to create two tables of primary keys:

PACKAGE company_pkg
IS
   /* Create a generic table type for primary keys */
   TYPE primary_keys_tabtype IS TABLE OF NUMBER NOT NULL
      INDEX BY BINARY_INTEGER;

   /* Declare two tables based on this table type */
   company_keys_tab primary_keys_tabtype;
   emp_keys_tab primary_keys_tabtype;

END company_pkg;