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
v_str VARCHAR2(4000 BYTE);
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);
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]
- Migrating OpenInsight(Multivalue) Database to Oracle(ORDBMS) - 3 Oracle ORDBMS support for multivalue columns (Version 8 onwards) Version...
- Migrating OpenInsight(Multivalue) Database to Oracle(RDBMS) - 1 What’s OpenInsight ? OpenInsight is a repository-based applications development environment...
- Migrating OpenInsight(Multivalue) Database to Oracle(RDBMS) - 2 Why do we need to Migrate ? Multivalue databases have...