Varray Examples

View All Scripts Login to Run Script

Script Name Varray Examples
Description The varray (variable size array) is one of the three types of collections in PL/SQL (associative array, nested table, varray). The varray's key distinguishing feature is that when you declare a varray type, you specify the maximum number of elements that can be defined in the varray. Yes, it is odd that the collection with any sense of a "fixed" maximum size is called the "variable size" array. But hey! More information: http://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS443
Area PL/SQL General
Contributor Steven Feuerstein
Created Monday December 14, 2015

Statement 1
You can define a varray type at the schema level of within a PL/SQL declaration section. This one can have no more than 10 elements.
Create Schema-Level Type
```
CREATE OR REPLACE TYPE list_of_names_t IS  
   VARRAY(10) OF VARCHAR2 (100); 
```
Type created.
Statement 2
You grant EXECUTE to give other schemas access to your schema-level type.
Let Others Use Your Type
```
GRANT EXECUTE ON list_of_names_t TO PUBLIC 
```
ORA-04088: error during execution of trigger 'SYS.DBCLOUD_BEFORE_DDL_DB_TRG' ORA-06512: at "SYS.DBCLOUD_SYS_SEC", line 1802 ORA-06512: at "SYS.DBCLOUD_SYS_SEC", line 2127 ORA-06512: at line 2

Statement 3

You can use EXTEND to make room in the varray for new elements, use COUNT, FIRST, LAST, etc., except...

All The Usual Methods

DECLARE  
   happyfamily   list_of_names_t := list_of_names_t ();  
BEGIN  
   happyfamily.EXTEND (4);  
   happyfamily (1) := 'Eli';  
   happyfamily (2) := 'Steven';  
   happyfamily (3) := 'Chris';  
   happyfamily (4) := 'Veva';  
     
   FOR l_row IN 1 .. happyfamily.COUNT  
   LOOP  
      DBMS_OUTPUT.put_line (happyfamily (l_row));  
   END LOOP;  
END;

Eli
Steven
Chris
Veva

Statement 4
Varrays always have consecutive subscripts, so you cannot delete individual elements except from the end by using the TRIM method. You can use DELETE without parameters to delete all elements. So this block fails.
Limitations on DELETE with Varray
```
DECLARE  
   happyfamily   list_of_names_t := list_of_names_t ();  
BEGIN  
   happyfamily.EXTEND (4);  
   happyfamily (1) := 'Eli';  
   happyfamily (2) := 'Steven';  
   happyfamily (3) := 'Chris';  
   happyfamily (4) := 'Veva';  
     
   happyfamily.delete (2);  
END; 
```
ORA-06550: line 10, column 4: PLS-00306: wrong number or types of arguments in call to 'DELETE'

Statement 5

You can apply SQL query logic to the contents a varray using the TABLE operator.

Use TABLE Operator with Varray

DECLARE  
   happyfamily   list_of_names_t := list_of_names_t ();  
 BEGIN  
   happyfamily.EXTEND (4);  
   happyfamily (1) := 'Eli';  
   happyfamily (2) := 'Steven';  
   happyfamily (3) := 'Chris';  
   happyfamily (4) := 'Veva';  
    
   /* Use TABLE operator to apply SQL operations to  
      a PL/SQL nested table */  
  
   FOR rec IN (  SELECT COLUMN_VALUE family_name  
                   FROM TABLE (happyfamily)  
               ORDER BY family_name)  
   LOOP  
      DBMS_OUTPUT.put_line (rec.family_name);  
   END LOOP;  
END;

Chris
Eli
Steven
Veva

Statement 6
You can define a column of varray type in a relational table. The next several steps demonstrate this.
Varray as Column Type in Database
```
CREATE OR REPLACE TYPE parent_names_t IS VARRAY (2) OF VARCHAR2 (100); 
```
Type created.
Statement 7
Can you see why this is a silly use of a varray?
Silly Varray!
```
CREATE OR REPLACE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100); 
```
Type created.

Statement 8

CREATE TABLE family 
( 
   surname          VARCHAR2 (1000) 
 , parent_names     parent_names_t 
 , children_names   child_names_t 
)

Table created.

Statement 9

Use native INSERT statement. So easy!

Insert Varrays into Table

DECLARE  
   parents    parent_names_t := parent_names_t ();  
   children   child_names_t := child_names_t ();  
BEGIN  
   DBMS_OUTPUT.put_line (parents.LIMIT);  
     
   parents.EXTEND (2);  
   parents (1) := 'Samuel';  
   parents (2) := 'Charina';  
   --  
   children.EXTEND;  
   children (1) := 'Feather';  
  
   --  
   INSERT INTO family (surname, parent_names, children_names)  
        VALUES ('Assurty', parents, children);  
  
   COMMIT;  
END;

Statement 10
```
SELECT * FROM family 
```
SURNAME PARENT_NAMES CHILDREN_NAMES
Assurty [unsupported data type] [unsupported data type]
Statement 11
You can modify the limit of a varray, as demonstratex in this script.
Modify Limit on Existing Varray
```
CREATE OR REPLACE TYPE names_vat AS VARRAY (10) OF VARCHAR2 (80); 
```
Type created.

SURNAME	PARENT_NAMES	CHILDREN_NAMES
Assurty	[unsupported data type]	[unsupported data type]

Statement 12

DECLARE 
   l_list   names_vat := names_vat (); 
BEGIN 
   DBMS_OUTPUT.put_line ('Limit of names_vat = ' || l_list.LIMIT); 
END;

Limit of names_vat = 10

Statement 13
You can modify the limit of a varray, as demonstrated in this script.
Modify Limit on Existing Varray
```
ALTER TYPE names_vat MODIFY LIMIT 100 INVALIDATE 
```
Type altered.

Statement 14

DECLARE 
   l_list   names_vat := names_vat (); 
BEGIN 
   DBMS_OUTPUT.put_line ('Limit of names_vat = ' || l_list.LIMIT); 
END;

Limit of names_vat = 100

Statement 15

Modify Limit on Existing Varray with Dynamic SQL

BEGIN 
   EXECUTE IMMEDIATE 'ALTER TYPE names_vat MODIFY LIMIT 200 INVALIDATE'; 
END;

Statement processed.

Statement 16

DECLARE 
   l_list   names_vat := names_vat (); 
BEGIN 
   DBMS_OUTPUT.put_line ('Limit of names_vat = ' || l_list.LIMIT); 
END;

Limit of names_vat = 200

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database