Informatica PowerCenter - How to get all occurrences of a string and its subsequent values from CLOB field in source table

Question

I have a CLOB field in the source Oracle table and would like to extract all occurrences of the string that matches the pattern 'RES_GetResData_Public_ScreenPrint' and its subsequent values (which are separated by '|' ) in Informatica PowerCenter.

Sample data -

|-1080|0833|RES_GetResData_Public_ScreenPrint010|F28028079|0820|3.3 02/17/14080|080|080|031|00879|[0-0]?[3.3 02/17/14-3.3 02/17/14]?[0833]|RES_GetResData_Public_ScreenPrint011|F28028081|080|080|080|080|032|-1080|032|-1032|-1080|032|-1080|032|-1080|0833|RES_GetResData_Public_ScreenPrint013|F28028007|0820|080|

Expected Result in Target -

|objectid|column1|column2|column3|column4|

|12345|RES_GetResData_Public_ScreenPrint010|F28028079|0820|3.3 02/17/14080|

|12345|RES_GetResData_Public_ScreenPrint011|F28028081|080|080|

|12345|RES_GetResData_Public_ScreenPrint013|F28028007|0820|080|

Answer 1

Use Java Transformation and write custom code. Or dump this to a flat file and have a second session that will use a bash command to split the CLOB content into rows, like:

cat *.xml | sed 's/RES_GetResData_Public_ScreenPrint/=-=RES_GetResData_Public_ScreenPrint/g' | awk '$0' RS="=-="

The above will first replace RES_GetResData_Public_ScreenPrint with =-=RES_GetResData_Public_ScreenPrint and then change that to rows splitting on =-= - as a result, your data will be intact, but split into rows.

Use that as input for your Source Qualifier and perform further transformations.