In this blog, we’ll discuss arrays in PL/SQL. The PL/SQL programing language provides a data structure called the VARRAY, which may store a fixed-size sequential collection of elements of an equivalent type. A varray is employed to store an ordered collection of data , however it’s often better to consider an array as a set of variables of an equivalent type.
All varrays contains contiguous memory locations. rock bottom address corresponds to the primary element and therefore the highest address to the last element.

An array may be a a part of collection type data and it stands for variable-size arrays. we’ll study other collection types during a later chapter ‘PL/SQL Collections’.
Each element during a varray has an index related to it. It also features a maximum size which will be changed dynamically.
Creating a Varray Type
A varray type is made with the CREATE TYPE statement. you want to specify the utmost size and therefore the sort of elements stored within the varray.
The basic syntax for creating a VARRAY type at the schema level is −
CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of
Where,
• varray_type_name may be a valid attribute name,
• n is that the number of elements (maximum) within the varray,
• element_type is that the data sort of the weather of the array.
Maximum size of a varray are often changed using the ALTER TYPE statement.
For example,
CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);
/

Type created.
The basic syntax for creating a VARRAY type within a PL/SQL block is −
TYPE varray_type_name IS VARRAY(n) of
For example −

TYPE namearray IS VARRAY(5) OF VARCHAR2(10); 
Type grades IS VARRAY(5) OF INTEGER;
Let us now compute on a couple of examples to know the concept −
Example 1
The following program illustrates the utilization of varrays −
DECLARE 
type namesarray IS VARRAY(5) OF VARCHAR2(10); 
type grades IS VARRAY(5) OF INTEGER; 
names namesarray; 
marks grades; 
total integer; 
BEGIN 
names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
marks:= grades(98, 97, 78, 87, 92); 
total := names.count; 
dbms_output.put_line('Total '|| total || ' Students'); 
FOR i in 1 .. total LOOP 
dbms_output.put_line('Student: ' || names(i) || ' 
Marks: ' || marks(i)); 
END LOOP; 
END; 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Total 5 Students
Student: Kavita Marks: 98
Student: Pritam Marks: 97
Student: Ayan Marks: 78
Student: Rishav Marks: 87
Student: Aziz Marks: 92

PL/SQL procedure successfully completed.
Please note −
• In Oracle environment, the starting index for varrays is usually 1.
• You can initialize the varray elements using the constructor method of the varray type, which has an equivalent name because the varray.
• Varrays are one-dimensional arrays.
• A varray is automatically NULL when it’s declared and must be initialized before its elements are often referenced.
Example 2
Elements of a varray could even be a %ROWTYPE of any database table or sort of any database table field. the subsequent example illustrates the concept.
We will use the purchasers table stored in our database as −
Select * from customers;

+—-+———-+—–+———–+———-+
| ID | NAME | AGE | ADDRESS | SALARY |
+—-+———-+—–+———–+———-+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+—-+———-+—–+———–+———-+
Following example makes the utilization of cursor, which you’ll study intimately during a separate chapter.

DECLARE 
CURSOR c_customers is 
SELECT name FROM customers; 
type c_list is varray (6) of consumers .name%type; 
name_list c_list := c_list(); 
counter integer :=0; 
BEGIN 
FOR n IN c_customers LOOP 
counter := counter + 1; 
name_list.extend; 
name_list(counter) := n.name; 
dbms_output.put_line('Customer('||counter ||'):'||name_list(counter)); 
END LOOP; 
END; 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Customer(1): Ramesh 
Customer(2): Khilan 
Customer(3): kaushik 
Customer(4): Chaitali 
Customer(5): Hardik 
Customer(6): Komal

PL/SQL procedure successfully completed.
So, this brings us to the end of blog. This Tecklearn ‘Arrays in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

532