Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




PL/SQL Table
Oracle Tips by Burleson

A PL/SQL table is very similar to an array in C or Pascal. Like a record, the PL/SQL table must be declared first as a type declaration and then as a variable of the user-defined type, as shown in Listing 2.15.

Listing 2.15 A PL/SQL table declaration.

   TYPE Student_SSN_tabtype IS TABLE OF
       integer (9)
       INDEX BY binary_integer;
   Student_SSN_table      Student_SSN_tabtype;

Like records, the PL/SQL table is a composite datatype. The number of rows that can be held in a PL/SQL table is limited only by the range of values for the INDEX variable. The PL/SQL table is indexed using a signed integer and can be navigated either forward or backward (unlike cursors, which can only be moved forward). Figure 2.9 illustrates the structure of a PL/SQL table.

As can be seen in the following line of code, references to records in a PL/SQL table are very similar to references to an array in C or Pascal.

Student_SSN_table (1)

The main difference in the reference is the use of parentheses instead of square brackets to reference the index variable.

TIP:  Referencing Elements In A PL/SQL Table

The elements in a PL/SQL table must be initialized to some value (even NULL) before you can make any other reference to it in your code. If you reference an element that has not been initialized to some value, a NO_DATA_FOUND exception will be raised. Unlike the scalar datatypes, a PL/SQL table element does not automatically contain a NULL value. The safest method for dealing with these elements is to initialize each element to NULL, even before attempting to populate the element with another value.

Prior to version 2.3, PL/SQL tables could only have a scalar datatype column in addition to the index column. PL/SQL version 2.3 allows composite datatypes (records and tables) to be referenced inside a PL/SQL table. This limitation was probably the primary factor limiting the use of PL/SQL tables in applications. PL/SQL tables will become more common now that they are not quite as unwieldy to use.

Variables and Constants

Declaring variables and constants in a PL/SQL block is quite simple, as shown by the following section of code:

   vStudentFirstName     varchar2 (12);
   <other variable declarations>
   YES     CONSTANT      char (1) := 'Y';

A constant must be initialized when it is declared. Variables may be initialized when they are declared or after the BEGIN statement. Once initialized, a PL/SQL variable may be referenced in an embedded SQL statement or an expression.

TIP:  Initializing Variables

It’s a good idea to initialize most variables when they are declared. If you don’t initialize a variable in the DECLARE subsection of the block, make sure you initialize it prior to using it in an expression or you’ll get stung by a comparison with a NULL value.


This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter