Oracle 12c VARRAY Demos

Oracle VARRAY Version 12.1.0.2

General Information

Library Note

[an error occurred while processing this directive]

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;
/