Preamble
In Oracle PL/SQL Varray (an array with variable size) is an array whose number of elements can vary from zero (empty) to the declared maximum size.
To access a Varray element, use the variable_name(index) syntax:
- The lower boundary of the index is 1; the upper boundary is the current number of elements.
- The upper limit changes when the elements are added or removed, but it cannot exceed the maximum size.
When you store and extract a varray from the database, its indexes and the order of the elements remain stable.
Syntax to define and then declare a Varrays type variable in Oracle PL/SQL
TYPE type_varray IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
v_arr type_varray;
Parameters and arguments of the array
- type_varray – name of type Varray
- element_type – any PL/SQL data type, except for REF CURSOR
- size_limit is a positive integer literal representing the maximum number of elements in the array.
- v_arr – the name of a variable of the Varray type
Note:
- When defining a Varray type, you must specify its maximum size.
An example of how to use Varray in Oracle PL/SQL
DECLARE
TYPE Foursome IS VARRAY(4) OF VARCHAR2(15); -- Varray type
-- is a varray variable initialized by the constructor:
team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');
PROCEDURE print_team (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
FOR i IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE(i) || '.' || team(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_team('2001 Team:');
team(3) := 'Pierre'; -- Change the values of the two elements
team(4) := 'Yvonne';
print_team('2005 Team:');
-- Call the constructor to assign new values to the Varray variable:
team := Foursome('Arun', 'Amitha', 'Allan', 'Mae');
print_team('2009 Team:');
END;
As a result, we get:
2001 Team:
1.John
2.Mary
3.Alberto
4.Juanita
---
2005 Team:
1.John
2.Mary
3.Pierre
4.Yvonne
---
2009 Team:
1.Arun
2.Amitha
3.Allan
4.Mae
---
In this example we defined Foursome as a local Varray type, declared a team variable of this type (initialized by the constructor) and defined the print_team procedure which printed Varray. The example calls the procedure three times:
- after initializing the variable,
- after changing values of two elements separately,
- and after using the constructor to change the value of all elements.
Using Varray
Varray should be used when:
- You know the maximum number of elements.
- You access the elements in sequence.
- Since you must store or retrieve all elements simultaneously, Varray may not be practical for a large number of elements.
PL/SQL tutorial: VARRAYs 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.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Database Performance with Enteros and AWS Resource Groups: A RevOps Approach to Streamlined Efficiency
- 13 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enhancing Healthcare Data Integrity: How Enteros, Logical Models, and Database Security Transform Healthcare Operations
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Budgeting and Cost Allocation in the Finance Sector with Enteros: A Smarter Approach to Financial Efficiency
- 12 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros and Cloud FinOps: Unleashing Big Data Potential for eCommerce Profitability
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…