Oracle VARRAY
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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
Database Security
Nested Tables
PLITBLM
REF Cursors
Types
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx