Oracle VARRAY
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
Purpose A varray is a variable element array object. Memory to store varrays is taken from the PGA.
AUTHID CURRENT_USER
Dependencies
ALL_NESTED_TABLES CDB_SOURCE DBA_SOURCE
ALL_SOURCE CDB_TYPES DBA_TYPES
ALL_TYPES CDB_VARRAYS DBA_VARRAYS
ALL_VARRAYS COL$ PLITBLM
CDB_NESTED_TABLES DBA_NESTED_TABLES TAB$
Documented Yes
First Available Not Known
Object Privileges GRANT EXECUTE ON
CREATE OR REPLACE NONEDITIONABLE TYPE uwclass.uwvarray AS
VARRAY(32767) OF VARCHAR2(32767);

GRANT EXECUTE ON uwclass.uwvarray TO sys;
System Privileges
ALTER ANY TYPE CREATE TYPE EXECUTE ANY TYPE
CREATE ANY TYPE DROP ANY TYPE UNDER ANY TYPE
 
Non-Object SQL
Define VARRAY as a TYPE CREATE OR REPLACE TYPE uwvarray AS VARRAY(5) OF NUMBER;
/

desc uwvarray

CREATE TABLE varraytab (
oddt_col VARCHAR2(20),
udva_col uwvarray);

desc varraytab
 
Object SQL
Define Object as a TYPE CREATE OR REPLACE TYPE Project AUTHID DEFINER AS OBJECT (
project_no NUMBER(2),
title      VARCHAR2(35),
cost       NUMBER(7,2));
/

desc project

SELECT type_name, typecode, instantiable
FROM user_types;
Define VARRAY of object type CREATE OR REPLACE TYPE ProjectList AS VARRAY(50) OF Project;
/

desc projectlist

SELECT type_name, typecode, instantiable
FROM user_types;
Create table with a VARRAY column -- create relational table department, which has a column of type ProjectList

CREATE TABLE department (
dept_id  NUMBER(2),
dname    VARCHAR2(15),
budget   NUMBER(11,2),
projects ProjectList);

desc department

set describe depth all linenum on indent on

desc department
Inserting into the VARRAY containing table -- each item in column projects is a varray that will store the projects scheduled for a given department.
-- next populate relational table department. Then the varray constructor ProjectList() provides values for column projects


INSERT INTO department
VALUES(30, 'Accounting', 1205700,
ProjectList (Project(1, 'Design New Expense Report', 3250),
Project(2, 'Outsource Payroll', 12350),
Project(3, 'Evaluate Merger Proposal', 2750),
Project(4, 'Audit Accounts Payable', 1425)));

INSERT INTO department
VALUES(50, 'Maintenance', 925300,
ProjectList (Project(1, 'Repair Leak in Roof', 2850),
Project(2, 'Install New Door Locks', 1700),
Project(3, 'Wash Front Windows', 975),
Project(4, 'Repair Faulty Wiring', 1350),
Project(5, 'Winterize Cooling System', 1125)));

INSERT INTO department
VALUES(60, 'Security', 750400,
ProjectList (Project(1, 'Issue New Employee Badges', 13500),
Project(2, 'Find Missing IC Chips', 2750),
Project(3, 'Upgrade Alarm System', 3350),
Project(4, 'Inspect Emergency Exits', 1900)));

COMMIT;

SELECT * FROM department;
Delete Record with VARRAY -- in the final example, delete the Accounting Department and its project list from table department

DELETE FROM department WHERE dept_id = 30;

SELECT * FROM department;
 
PL/SQL
Delete Record with VARRAY CREATE OR REPLACE TYPE Project IS OBJECT (
project_no NUMBER(2),
title      VARCHAR2(35),
cost       NUMBER(7,2));
/

DECLARE
 TYPE ProjectList IS VARRAY(50) OF Project;
 demo_t ProjectList;
BEGIN
  NULL;
END;
/

-- the following generates an error
DECLARE
 TYPE ProjectList IS VARRAY(50) OF Project;
 demo_t ProjectList
;
BEGIN
  demo_t(1) := Project(1,'One',1);
END;
/

set serveroutput on

DECLARE
 TYPE ProjectList IS VARRAY(50) OF Project;
 demo_t ProjectList;

BEGIN
  demo_t := ProjectList(NULL,NULL,NULL);
  demo_t(1) := Project(1,'One',1);
  demo_t(2) := Project(2,'Two',2);


  dbms_output.put_line(demo_t(2).title);  -- value of varray element
  dbms_output.put_line(demo_t.COUNT);     -- current count of elements in varray
  dbms_output.put_line(demo_t.LIMIT);     -- max elements in varray

  IF demo_t.EXISTS(2) THEN
    dbms_output.put_line('TRUE');
  END IF;
END;
/

Related Topics
Built-in Functions
Built-in Packages
Collections
Nested Tables
PLITBLM
REF Cursors
Types
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved