Parsing a CSV string can be done more effectively with SUBSTR, INSTR and associative arrays.
Most of my inspiration is coming from this post.
SET SERVEROUTPUT ON; DECLARE TYPE MSG_LABELS_TYPE IS TABLE OF VARCHAR2(400) INDEX BY VARCHAR2(50); v_delimpos1 PLS_INTEGER; v_delimpos2 PLS_INTEGER; p_delim1 VARCHAR2(1); p_delim2 VARCHAR2(1); INPUT_STRING VARCHAR2(500); v_label varchar(50); v_value varchar(400); v_result MSG_LABELS_TYPE; BEGIN INPUT_STRING := 'InterfaceID=ACMEPIPPOConnector;TechnicalMessageID=ACMEPIPPOConnector^INVOICE^f6de3e52000001404d914d04ffff847a^7-382734;EventType=ACMEMessage For PIPPO Posted;PathOfService=ACME_CommonServices/ProxyServices/ACMECommonServices_NESOA_to_PIPPO_PS;EventOccuredOn=2013-08-05T10:22:11.765+02:00;BusinessID=7-382734;ServerName=osbpr1ms3'; p_delim1 := ';'; p_delim2 := '='; INPUT_STRING := INPUT_STRING || ';'; v_delimpos1 := INSTR(INPUT_STRING, p_delim1); while v_delimpos1 > 0 and LENGTH(INPUT_STRING) > 1 loop v_delimpos2 := INSTR(INPUT_STRING, p_delim2); v_label := SUBSTR(INPUT_STRING, 1, v_delimpos2 - 1); v_value := SUBSTR(INPUT_STRING, v_delimpos2 + 1, v_delimpos1 - v_delimpos2 - 1); v_result(v_label) := v_value; INPUT_STRING := SUBSTR(INPUT_STRING, v_delimpos1 + 1); v_delimpos1 := INSTR(INPUT_STRING, p_delim1); END LOOP; dbms_output.put_line('InterfaceID ' || v_result('InterfaceID')); dbms_output.put_line('TechnicalMessageID ' || v_result('TechnicalMessageID')); dbms_output.put_line('EventType ' || v_result('EventType')); dbms_output.put_line('PathOfService ' || v_result('PathOfService')); dbms_output.put_line('EventOccuredOn ' || v_result('EventOccuredOn')); dbms_output.put_line('BusinessID ' || v_result('BusinessID')); dbms_output.put_line('ServerName ' || v_result('ServerName')); END; /
More info on Collections here.
No comments:
Post a Comment