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

In 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 in to single field separated by value Delimiter.

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 in to collection table and convert to the multi-value column

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

Result

 

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 provides more advanced ORDBMS features.

[to be continued in ORDBMS series of Oracle]