Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


VARRAY type in PL/SQL

Using varrays in Oracle SQL *Plus

This article will demonstrate various ways to use varying array types: how to declare them, how to access data members, how to use with cursors and tables.

Initialized Value PL/SQL VARRARY

To begin, open SQL *Plus. Create an anonymous code block declaring a VARRAY type of 3 integers (Line 2), then a variable of the named type (int_varray) and assign NULL to each of the 3 members (Line 4). Using a FOR loop, iterate through the null varray (Lines 8-12). Assign values to each of the 3 members (Lines 14-16) and create a 2nd FOR loop to iterate through the varray again to show the assigned values (Lines 21-24).

varray sample

Notice that the values for the null varray are blank and that the assigned values are printed out correctly.

varray output

Using Extend for PL/SQL VARRARY

In a new block of code, the varray type and variable are declared once again, however the values are not initialized. This code uses the Extend method(Line 12) of the varray type to add members to the varray and then initialize them.

varray extend

The output appears to contain the 3 members of the varray and their assigned values from the FOR loop.

varray extend output

Using VARRAY type in a Cursor

The next sample will use a loop and a cursor to iterate through a table of students and their corresponding test scores and use a varray to output a grade letter for them based on the score. Create a table named studentExam with a varchar2 studentName and int Test_score.

Create table

Create several Insert statements to populate the table with studentNames and test scores.

Insert statements

In the declare section (Lines 2-10) of the code block, a grade_varray type is created and a variable of grade_varray type is declared. A cursor for the studentExam data is declared as is an integer placeholder and exam_grade variable to output the grade with the student exam data.

The FOR loop to add varray members and assign values is created after the BEGIN statement (Lines 12-22). This code is counting from 1 to 5 and then adding the correct i_chr integer values using a CASE statement to the value of 64. A CHR function is used to populate the correct grade letter (A-F) into the varray.

The exam_cursor is opened (Line 26) and iterated through using a CASE statement to check the test_score against a range of values to determine which varray member to assign to the variable exam_grade.

Code Block

Note the check of CHR(65) against DUAL to verify that the ASCII value for the capital letter A is 65.

ASCII value

The Grade letters are output to prove that they were correctly assigned to the varray. The StudentName, scores and grades are also output. Note that they are evenly spaced because the RPAD function was used in the code block.

Formatted Output

Note: This is the unformatted version of the output (without RPAD).

Unformatted output

Creating VARRAY Types for Tables

The next sample uses a declared database varray type in a table. First, create the varray type of 5 varchar2 memeber then a table called emps to hold the empID, empName and the address in the varray data type.

varray type and table

To insert a value into this table, the addr_varray must be called as part of the values to be inserted. Each of the 5 members are assigned a value here, but it is not necessary.

Insert statement

Accessing VARRAY Table Types - SELECT Statement

To access the contents of the varray column, a nested table type will need to be created and used in the Select statement.

Nested table type

The select statement uses the reserved word column_value to access the rows in the nested table. The reserved word THE tells the column_value that it will be selected from a nested table query. A Cast on the varray datatype converts it to the nested table type. The result is a single column with the data rows entered from the emps table.

Select column_value

To view the empname with the data, a Union query would be required. Trying to view the data as a single call from the table will result in an error.

Subquery error

Using the Union query without some creative row numbering retrieves the data, but not in the order expected.

column_value sort

Using 0 for the ordernum for the empname in a Union with the column_name values sorted by rowNum retrieves the data in the correct order.

column_value sorted correctly

Additional VARRAY Methods

In addition, there are several other methods for use with varrays. This example shows how to use COUNT, TRIM and DELETE within a code block. Note that TRIM is not specifying which value to remove from the varray, so the last value in the array is the one removed. Delete removes all members.

varray additional methods