Oracle ORDBMS support for multivalue columns (Version 8 onwards)
Version 8.0 and higher versions of Oracle are referred to as ORDBMS (Object-Relational Database Management System). The traditional Oracle database management system is extended to include Object-Oriented Concepts and structures such as abstract data types, nested tables, varying arrays, object views and references.
MultiValue columns through Collections feature of Oracle 8
“A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.
Oracle offers the following persistent collections:
- Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
- Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
PL/SQL provides the following additional non-persistent collection (cannot be stored in database tables):
- Index-by tables, also known as associative arrays, lets you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
Example 1: Table with Collection Column Type
- SQL to create a
create or replace
TYPE Varchar2Table IS TABLE OF VARCHAR2(4000 BYTE);
- Using collection as a column in a table:
CREATE TABLE BOOK
ID ROWID NOT NULL
, NAME VARCHAR2(200 CHAR) NOT NULL
, CONTENTS SABARI.VARCHAR2TABLE
, CONSTRAINT BOOK_PK PRIMARY KEY
NESTED TABLE CONTENTS STORE AS BOOK_CONTENTS RETURN AS VALUE;
ALTER TABLE BOOK
ADD CONSTRAINT BOOK_UK_NAME UNIQUE
The above statement creates 2 tables with named “
BOOK” and “
So whenever we store contents to
Book table, book lines will be inserted to the “
Inserting rows into collections:
INSERT INTO BOOK(ID,NAME, CONTENTS) VALUES _
(1 , ‘Book1', Varchar2Table(‘line1', ‘line2', ‘line3', ‘line4'));
SELECT * FROM BOOK
Example 2: Creating Collections on the Fly
Oracle 10g has introduced an extremely useful new group function,
COLLECT. This function enables us to aggregate data into a collection, retaining multiple records of data within a single row (like a nested table). One of the main benefits of this function is that it makes “string aggregation” (one of the web’s most-requested Oracle technique) very simple.
So by using this feature, we can manipulate multivalue column views even from normal RDBMS columns.
SELECT CAST( COLLECT( NAME ) AS Varchar2Table ) FROM BOOK
- The statement is simple
Varchar2 data type.
COLLECT function collects values in different rows into collection.
CAST function casts the return values to be accessible by user defined
Varchar2Table data type.
[To be continued...]
- All data and information provided on this page is for informational purposes only. Some parts of this tutorial are taken from the specified links and references. The mentioned writings belong to their corresponding authors.
- The opinions expressed herein are my own personal opinions and do not represent my employer’s view in any way.
- Migrating OpenInsight(Multivalue) Database to Oracle(RDBMS) -1
What’s OpenInsight ? OpenInsight is a repository-based applications development environment...
- Migrating OpenInsight(Multivalue) Database to Oracle(RDBMS) -2
Why do we need to Migrate ? Multivalue databases have...