cancel
Showing results for 
Search instead for 
Did you mean: 

How do we handle CLOB datatype columns in Incorta?

amit_kothari
Employee
Employee

How do we handle CLOB datatype columns in Incorta?

1 REPLY 1

amit_kothari
Employee
Employee

Incorta cannot directly ingest CLOB datatypes, what we can do is to use some sql string functions to parse out the values we need directly in the extract. For eg in Siebel the Audit table has a clob field called audit_log which has the old and new values for any changes. Here are two examples from oracle database- 

1)  This select parses out the new value of the timestamp - 

SELECT
  tbl_record_id,
   to_date(REGEXP_SUBSTR(dbms_lob.substr(audit_log,300,1), '[1-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]'),'yyyy-mm-dd HH24:mi:ss')  new_val,
  operation_dt
FROM
  siebel.s_audit_item
WHERE instr(audit_log,'X_ALT_RESL_DATE') >= 1
AND tbl_name='S_EVT_ACT'

2)  This select parses out the new value of a status based on a pattern- 

select tbl_record_id,
substr(AUDIT_LOG,instr(AUDIT_LOG,'*',1,4)+1,instr(AUDIT_LOG,'*',1,5)-instr(AUDIT_LOG,'*',1,4)-2) as new_val,
operation_dt
from siebel.s_audit_item
where tbl_name='S_EVT_ACT_X'
and instr(AUDIT_LOG,'ATTRIB_46') >= 1