Click here to Skip to main content
13,553,262 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

11.3K views
3 bookmarked
Posted 8 Jul 2010
Licenced CPOL

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

, 8 Jul 2010
Rate this:
Please Sign up or sign in to vote.
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:
    create or replace
    TYPE Varchar2Table IS TABLE OF VARCHAR2(4000 BYTE);
  2. 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
    (
    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:

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

Query table:

SELECT * FROM BOOK

image

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

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

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.

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...

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sabarinathan A
Architect
India India
I have been programming for last 20 years on various platforms including .NET, Visual Basic 6, Oracle and SQL server.

I decided to write articles when I have free time hoping to share my thoughts with you.

To know more about me visit http://sabarinathanarthanari.com

You may also be interested in...

Comments and Discussions

 
GeneralFlexible datattype Pin
Ajay Kale New11-Nov-10 0:27
memberAjay Kale New11-Nov-10 0:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180515.1 | Last Updated 8 Jul 2010
Article Copyright 2010 by Sabarinathan A
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid