Syntax to fetch pending transfer details from Data_cache column in hrc_txn_data

Nirmal Kumar L

Summary

Syntax to fetch pending transfer details from Data_cache column in hrc_txn_data

Content

Hi,

We need to fetch the pending transaction Transfer details like New Location,New Position,New Grade,New Job etc. We are aware that We can capture these details in data_cache column in hrc_txn_data table. As these column captures many entries, how can we fetch only one value(say New location, New Position,New Grade,New job etc.)? Is there any syntax to fetch that alone? Please suggest.

Thanks,
Nirmal kumar 

User_OWH0W

Hi Nirmal,

Below code would fetch you New location details from data cache column. You need to replcae the value which you want to fetch in below code. Let me know if any doubt.

 

select regexp_replace(substr(Q.LocationId_New,instr(Q.LocationId_New,'<',1) +10,instr(Q.LocationId_New,'</')-11),'<|>|OldValue|</|/>|NewValue','') LocationId_New

From

(Select xmltype(txndata.DATA_CACHE).extract('.//Compare/AttributeName[text()="LocationId"]/../NewValue').getStringVal() LocationId_New,transaction_id
 from hrc_txn_data txndata ) Q

 

Thanks

Vivek