For how many years have you been working
with physical servers that are starving your database of the memory
necessary to deploy important new performance features such as the Result
Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and
Full Database Caching? Too long? Contact me to learn how to improve all
queries ... not just some queries.
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;
/
-- 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;
/
