CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle VARRAYS
Version 11.1
 
General Information
A varray is a variable element array object

Related Data Dictionary Objects
col$ tab$ user_varrays
dba_source all_source user_source
dba_types all_types user_types
dba_tables all_tables user_tables
dba_tab_cols all_tab_cols user_tab_cols
dba_nested_tables all_nested_tables user_nested_tables
System Privileges
CREATE TYPE CREATE ANY TYPE DROP ANY TYPE
 

Define Object as a TYPE
CREATE OR REPLACE TYPE Project 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 based on VARRAY
Create relational table department, which has a column of type ProjectList

CREATE TABLE department (
dept_id  NUMBER(2),
name     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.

Now, you are ready to populate relational table department.
In the following example, notice how 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)));

SELECT * FROM department;

Delete Record with VARRAY
In the final example, you delete the Accounting Department
and its project list from table department:


DELETE FROM department WHERE dept_id = 30;

SELECT * FROM department;
 
Related Topics
Collections
Nested Tables
REF Cursors
Types
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [103 users online]    © 2010 psoug.org