Click here to Skip to main content
15,615,280 members
Articles / All Topics
Technical Blog
Posted 16 Jul 2010

Tagged as



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

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
16 Jul 2010CPOL1 min read
Using simple PL/SQL function to aggregate individual collection values in to single field separated by value Delimiter

In the previous post, we saw techniques to create collections on the fly to simulate the multi-value columns in Oracle. In this part, we use simple PL/SQL function to aggregate individual collection values into a single field separated by value Delimiter.

The following function creates multi-valued string from the Oracle collection. More info.

FUNCTION To_String (      
  Nt_in        IN Varchar2Table, 
  Delimiter_in IN VARCHAR2 DEFAULT ‘,’ 
     v_idx PLS_INTEGER; 
     v_str VARCHAR2(4000 BYTE); 
     v_dlm VARCHAR2(10); 
     v_idx := nt_in.FIRST; 
        v_str := v_str || v_dlm || nt_in(v_idx); 
        v_dlm := delimiter_in; 
        v_idx := nt_in.NEXT(v_idx); 
     END LOOP; 
     RETURN v_str; 
  END To_String; 

The above function helps developer to pass values to Business layer while migrating multi-value column to Oracle.

The following Query will first aggregate the rows into collection table and convert to the multi-value column:

SELECT migration.to_string( CAST( COLLECT( NAME ) AS Varchar2Table ) ) FROM EMPLOYEEADDRESS



Migrating Sub-Values to Oracle

Oracle supports ORDBMS concepts from version 8i which allows users to create user defined types and include them as column datatypes (Complex type in SQL developer).

Prior to Oracle 9i, collections could only be used to represent a single dimension of information (a list of names or salaries). With Oracle 9i and support for multi-level collections, PL/SQL developers can now model multi-dimensional phenomena. More info.

In addition to sub-value enhancements, Oracle 10, 11 versions provide more advanced ORDBMS features.

[to be continued in ORDBMS series of Oracle]

Related Posts

  1. Migrating OpenInsight(Multivalue) Database to Oracle(ORDBMS) - 3 Oracle ORDBMS support for multivalue columns (Version 8 onwards) Version...
  2. Migrating OpenInsight(Multivalue) Database to Oracle(RDBMS) - 1 What’s OpenInsight ? OpenInsight is a repository-based applications development environment...
  3. Migrating OpenInsight(Multivalue) Database to Oracle(RDBMS) - 2 Why do we need to Migrate ? Multivalue databases have...


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

Written By
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

Comments and Discussions

GeneralFlexible datatype Pin
Ajay Kale New11-Nov-10 0:30
Ajay Kale New11-Nov-10 0:30 
Hi ,

I need help for oracle datatype-

I am using TEMP_STRING VARCHAR2(32767);

but while stroring in it I am building string more than 32767(so exception) ..which is around 40000+
So I used LONG datatype - but still the same.
Will it be appropriate or is there any other one in Oracle ?

Can you please suggest.

Thanks and regards,
- Ajay K

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.