Example on Flatten Component - ODI 12c

To process input data with complex structure and produces the flattened representation of the same data using standard datatypes

Scenario:
Input Data:

Output Data:

Scripts:

CREATE OR REPLACE TYPE node AS TABLE OF VARCHAR2(30);
/
CREATE TABLE nested_table (id NUMBER, NodeName node)
       NESTED TABLE NodeName STORE AS NodeName_tab;
/
INSERT INTO nested_table VALUES (1, node('A'));
INSERT INTO nested_table VALUES (2, node('B', 'C'));
INSERT INTO nested_table VALUES (3, node('D', 'E', 'F'));
COMMIT;
/
SELECT * FROM nested_table;


ODI Steps:
-------------

1. Create Mapping
2. Drag and drop source data store (NESTED_TABLE) from source model.
3. Drag and drop flatten component from component palette into mapping editor
4. Drag and drop Target data store (CONVERTED_NESTED_TABLE) from target Model
5. Connect output connector of flatten component to input connector of Target data Store
6. Connect output connector of Source data store to input connector of flatten Component as mentioned below

7. Map ID column from source data store to Flatten component directly
8. Map Node Column as mentioned below

9. Select complex attribute type as mentioned below on flatten component

10. Execute it

Output: