How do we handle CLOB datatype columns in Incorta?

amit_kothari — Fri, 08 Apr 2022 15:53:30 GMT

Re: How do we handle CLOB datatype columns in Incorta?

amit_kothari — Fri, 08 Apr 2022 15:54:53 GMT

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

topic How do we handle CLOB datatype columns in Incorta? in Data & Schema Discussions

How do we handle CLOB datatype columns in Incorta?

Re: How do we handle CLOB datatype columns in Incorta?