"InterfaceID=Common_NCRS;TechnicalMessageID=Common_NCRS^ACMEPreOrder.quote_order^5505352925343722746--4337ac89.1404f3a3d07.-8bc;EventType=InvokedACME;PathOfService=Commons_NCRS/ProxyServices/Common_NCRS_PS;EventOccuredOn=2013-08-05T18:17:59.470+02:00;BusinessID=DE11PC089^d3a59751-38e4-eed5-7bf7-4f45592a9d18;ServerName=osbpr1ms2" and this labelname "EventOccuredOn", the following function extracts the value "2013-08-05T18:17:59.470+02:00" :
create or replace FUNCTION ACME_findLabelValue (labels IN VARCHAR2, labelname IN VARCHAR2 ) return VARCHAR2 is begin return REPLACE(REGEXP_SUBSTR(labels, labelname || '=[^;]+'), labelname || '=', ''); end;
Performance was abysmal. I have replaced with this much faster function:
create or replace FUNCTION ACME_findLabelValue (labels IN VARCHAR2, labelname IN VARCHAR2 ) return VARCHAR2 is v_delimpos1 PLS_INTEGER; v_delimpos2 PLS_INTEGER; labels2 VARCHAR2(4000); begin v_delimpos1 := INSTR(labels, labelname || '=' ); if v_delimpos1 > 0 then labels2 := SUBSTR(labels, v_delimpos1 + 1 + LENGTH(labelname)); v_delimpos2 := INSTR(labels2, ';' ); return SUBSTR(labels2, 1, v_delimpos2 - 1); else return ''; end if; end;
No comments:
Post a Comment