Oracle PL/SQL Collections - Adding Elements to an Existing Table

Oracle PL/SQL Collections - Adding Elements to an Existing Table

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 1 min read

Here I am giving an example of a PL/SQL program to add elements to an existing table (collection).

Although a table is unconstrained, you cannot assign to an element that does not exist yet, and would thus cause the table to increase in size. if you attempt to do this, PL/SQL will raise the error "ORA-6533: Subscript beyond count" which is equivalent to the SUBSCRIPT_BEYOND_COUNT predefined exception. This is illustrated by the following example:

Example - Adding Elements to an Existing Table in PL/SQL

DECLARE
TYPE t_NumbersTab IS TABLE OF NUMBER;
v_Numbers t_NumbersTab := t_NumbersTab(1, 2, 3);
BEGIN
--v_Numbers was initialized to have 3 elements. So the
--following assignment are all legal.
v_Numbers(1) := 7;
v_Numbers(2) := -1;

--However, this assignment will raise ORA6533.
v_Numbers(4) := 4;
END;

TIP

You can increase the size of a nested table by using the EXTEND method.

See also: