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

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:

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