Oracle EXTEND method.

Preamble

In Oracle PL/SQL, the EXTEND method is a procedure that adds elements to the end of Varray or Nested Tables. The collection can be empty, but not NULL.

Syntax of EXTEND method in Oracle PL/SQL

collection_name.EXTEND;
collection_name.EXTEND (n);
collection_name.EXTEND (n, i);

Method parameters and arguments

• collection_name – the name of the collection.
• EXTEND – adds one zero element to the collection.
• EXTEND (n) – adds n zero elements to the collection.
• EXTEND (n, i) – adds n copies of the i-th element to the collection.

Note:

• EXTEND (n, i) is the only form you can use for a collection whose elements have a NOT NULL limitation.
• EXTEND works with the internal size of the collection. That is, if DELETE removes an item but saves its filler,
• EXTEND assumes that the item exists.

Let’s look at an example to understand how to use the EXTEND collection method in Oracle PL/SQL:

EXTEND example with Nested Tables

• Consider an example where we declare the Nested Tables variable by initializing it with three elements; we add two copies of the first element;
• remove the fifth (last) element; and then add one zero element.

Since EXTEND believes that the removed fifth element exists, the added zero element is the sixth element. The print_nt procedure prints the Nested Tables variable after initialization and after EXTEND and DELETE operations.

CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS
i NUMBER;
BEGIN
i := nt.FIRST;

IF i IS NULL THEN
DBMS_OUTPUT.PUT_LINE('nt is empty');
ELSE
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT('nt.(' || i || ') = '); print(nt(i));
i := nt.NEXT(i);
END LOOP;
END IF;

DBMS_OUTPUT.PUT_LINE('---');
END print_nt;

DECLARE
nt nt_type := nt_type(11, 22, 33);
BEGIN
print_nt(nt);

nt.EXTEND(2,1); -- Add two copies of the first element
print_nt(nt);

nt.DELETE(5); -- Remove the fifth element
print_nt(nt);

nt.EXTEND; -- Add one zero element
print_nt(nt);
END;
Result:
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(5) = 11
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(6) = NULL
---

PL/SQL tutorial: PL/SQL Collection Method EXTEND in Oracle Database

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.