Reading data from Oracle VARRAY data type field - Informatica PowerCenter

VARRAY is an Oracle data type used to support columns containing multivalued attributes

Syntax for Varray:

CREATE OR REPLACE TYPE Vcol1 AS VARRAY(5) OF VARCHAR2(20);

/

CREATE TABLE varray_table (Field1 number, Field2 Vcol1);

Requirement: (Below was the requirement posted in a forum?)

Data in Table 1:

- F1 = "A;B;C"

- F2 = "Informatica"

- F3 = "Power"

But when I run the workflow and the mapping to extract this data, Informatica extract this data and load it in Table 2 like that

 ( F1 is a field to type "Array" and dimension 3 )

Data in Table 2 :

- F1 = "A"        =>        F1[0]

- F2 = "B"        =>        F1[1]

- F3 = "C"        =>        F1[2]

My problem is that I want to get same data in the same field in Table 1 and Table2 :

I want this in my Table 2 :

- F1 = "A;B;C"

- F2 = "Informatica"

- F3 = "Power"

Solution:

Informatica doesn't support "Varry table data type". It will be imported as Varchar2 by default within Informatica.

so make sure to change the source qualifier's sql query as below. (Assuming Oracle 11g been used)

WITH T AS (

   SELECT ROWID a, F2, F3, T2.COLUMN_VALUE col

   FROM Table1 T1, TABLE(T1.F1) T2)

   SELECT LTRIM (SYS_CONNECT_BY_PATH(COL,';'),';') COL, F2, F3

   FROM (

   SELECT a, F2, F3, COL, ROW_NUMBER() OVER ( partition by a order by a) FILA

   FROM T)

   WHERE CONNECT_BY_ISLEAF=1

   START WITH FILA =1

   CONNECT BY PRIOR a = a

   and prior FILA+1=FILA

  

Output:

F1          F2               F3

A;B;C     Informatica  Power