PL/SQL VARRAY

  • Array in other languages – VARRAY in PL/SQL
  • Collection of items of same datatype.
  • When defining a VARRAY type, need to specify its maximum size. So upper bound is fixed.
  • Subscript is integer (i.e. index) starts from 1
  • VARRAY is always dense (consecutive subscript). You cannot delete an item in middle, but we can trim elements from end.
  • VARRAY is preferred when no. of elements known & accessed using sequence i.e. index.

VArray Example:

TYPE va_int_type IS VARRAY(7) OF NUMBER;
TYPE va_int_type IS VARRAY(7) OF VARCHAR2(100);
Index1234567
Value21344445777899

You cannot add elements after 7th index. As upper bound is fixed at the time of array creation. You can access element using index like A[2]

Please refer below GitHub code for more details.

VArray of Composite Datatype:

CREATE TYPE emails AS OBJECT (
    email_id       VARCHAR2(100),
    email_type     VARCHAR2(100),
    primary_email  CHAR(1)
);

CREATE OR REPLACE TYPE emaillist AS
    VARRAY(5) OF emails;
/

Reference: https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/05_colls.htm

Thanks!

Happy Learning! Your feedback would be appreciated!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s