Migrating OpenInsight (Multivalue) Database to Oracle(ORDBMS) -3

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

1. SQL to create a string collection:
   SQL

   create or replace
   TYPE Varchar2Table IS TABLE OF VARCHAR2(4000 BYTE);

2. Using collection as a column in a table:
   SQL

   CREATE TABLE BOOK
   (
   ID ROWID NOT NULL
   , NAME VARCHAR2(200 CHAR) NOT NULL
   , CONTENTS SABARI.VARCHAR2TABLE
   , CONSTRAINT BOOK_PK PRIMARY KEY
   (
   ID
   )
   ENABLE
   )
   NESTED TABLE CONTENTS STORE AS BOOK_CONTENTS RETURN AS VALUE;
   
   ALTER TABLE BOOK
   ADD CONSTRAINT BOOK_UK_NAME UNIQUE
   (
   NAME
   )
   ENABLE;

   The above statement creates 2 tables with named “BOOK” and “BOOK_CONTENTS” !

   So whenever we store contents to Book table, book lines will be inserted to the “BOOK_CONTENTS” table.

Inserting rows into collections:

SQL

INSERT INTO BOOK(ID,NAME, CONTENTS) VALUES _
(1 , ‘Book1', Varchar2Table(‘line1', ‘line2', ‘line3', ‘line4'));

Query table:

SQL

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.

Example

SQL

SELECT CAST( COLLECT( NAME ) AS Varchar2Table ) FROM BOOK

• The statement is simple NAME is Varchar2 data type.
• The 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...]

References

• http://sheikyerbouti.developpez.com/collections/collections.htm
• http://www.oracle-developer.net/display.php?id=306

Disclaimer

1. 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.
2. The opinions expressed herein are my own personal opinions and do not represent my employer’s view in any way.

CodeProject

Related Posts

1. Migrating OpenInsight(Multivalue) Database to Oracle(RDBMS) -1
   What’s OpenInsight ? OpenInsight is a repository-based applications development environment...
2. Migrating OpenInsight(Multivalue) Database to Oracle(RDBMS) -2
   Why do we need to Migrate ? Multivalue databases have...