Click here to Skip to main content
13,735,592 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

9.5K views
Posted 16 Jul 2010
Licenced CPOL

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

, 16 Jul 2010
Rate this:
Please Sign up or sign in to vote.
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 ‘,’ 
  ) RETURN VARCHAR2  
IS 
     v_idx PLS_INTEGER; 
     v_str VARCHAR2(4000 BYTE); 
     v_dlm VARCHAR2(10); 
  BEGIN 
     v_idx := nt_in.FIRST; 
     WHILE v_idx IS NOT NULL LOOP 
        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

Result:

image 

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

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 datatype Pin
Ajay Kale New11-Nov-10 0:30
memberAjay Kale New11-Nov-10 0:30 

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 | Cookies | Terms of Use | Mobile
Web06-2016 | 2.8.180920.1 | Last Updated 16 Jul 2010
Article Copyright 2010 by Sabarinathan A
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid