home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  

10.4 Declaring a PL/SQL Table

As with a record, a PL/SQL table is declared in two stages:

  • Define a particular PL/SQL table structure (made up of strings, dates, etc.) using the table TYPE statement. The result of this statement is a datatype you can use in declaration statements.

  • Declare the actual table based on that table type. The declaration of a PL/SQL table is a specific instance of a generic datatype.

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 ]

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

TYPE reports_requested_tabtype IS TABLE OF VARCHAR2 (100)

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
   /* Create a generic table type for primary keys */
   TYPE primary_keys_tabtype IS TABLE OF NUMBER NOT NULL

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

END company_pkg;

Previous: 10.3 PL/SQL Tables and DML Statements Oracle PL/SQL Programming, 2nd Edition Next: 10.5 Referencing and Modifying PL/SQL Table Rows
10.3 PL/SQL Tables and DML Statements Book Index 10.5 Referencing and Modifying PL/SQL Table Rows

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference