To implement string segmentation, the algorithm is as follows:
Algorithm 1:
DECLARERemove_column myvarray_list; Xvarchar( +); Subvarchar( +); I Number; J Number; C Number; Rcount Number;BEGINRemove_column:=Myvarray_list ();--init array.Sub:='ORA-26786: The key is ("AAC001") = (11370911196606055225) The row exists, but has conflicting columns "AAC003", "AAE011", "AAE036", "AAE476" (in table BI3. AC01) ORA-01403: No data found'; --sub:= ' ora-26786:a row with key ("C1", "C2") = (TEST1, TEST1) exists but had conflicting column (s) "C4" in table SK. T1 ora-01403:no data found ';I=INSTR (Sub,'=',1,1); Sub:=SUBSTR (Sub, i); I:=INSTR (Sub,' "',1,1); Sub:=SUBSTR (Sub, i); I:=INSTR (Sub,'"',-1,1); Sub:=SUBSTR (Sub,1, i); Sub:=REPLACE(Sub,'"',"'); Sub:=REPLACE(Sub,' ',"'); --Dbms_output. Put_Line (sub); ------------------------------------------------------------J:=1; Rcount:=1; whileINSTR (Sub,',',1, j)!=0LOOP I:=INSTR (Sub,',',1, J); IFJ= 1 ThenC:=1; ELSEC:=1+INSTR (Sub,',',1J-1); END IF; X:=SUBSTR (sub,c,i-c); J:=J+1; --Dbms_output. Put_Line (x);Remove_column.extend; Remove_column (rcount):=x; Rcount:=Rcount+1; ENDLOOP; IFJ> 1 ThenI:=INSTR (Sub,',',1J-1); X:=SUBSTR (sub,i+1); --Dbms_output. Put_Line (x); ELSEx:=Sub; --Dbms_output. Put_Line (x); END IF; Remove_column.extend; Remove_column (rcount):=x; forXinch 1.. Remove_column.CountLoop Dbms_output.put_line (Remove_column (x)); ENDLoop;END;
Algorithm 2:
DECLAREStr_split myvarray_list; P_strvarchar( +); JINT:= 0; IINT:= 1; LensINT:= 0; LENPINT:= 0; P_delimiterVARCHAR(1) := ','; Str VARCHAR2( +);BEGINStr_split:=Myvarray_list ();--init array.P_str:='ORA-26786: The key is ("AAC001") = (11370911196606055225) The row exists, but has conflicting columns "AAC003", "AAE011", "AAE036", "AAE476" (in table BI3. AC01) ORA-01403: No data found'; --p_str:= ' ora-26786:a row with key ("C1", "C2") = (TEST1, TEST1) exists but have conflicting column (s) "C4", "C3" in tab Le SK. T1 ora-01403:no data found ';I=INSTR (P_STR,'=',1,1); P_STR:=SUBSTR (P_str, i); I:=INSTR (P_STR,' "',1,1); P_STR:=SUBSTR (P_str, i); I:=INSTR (P_STR,'"',-1,1); P_STR:=SUBSTR (P_STR,1, i); P_STR:=REPLACE(P_str,'"',"'); P_STR:=REPLACE(P_str,' ',"'); ------------------------------------------------------------Lens:=LENGTH (P_STR); LENP:=LENGTH (P_delimiter); Dbms_output.put_line (lens); I:= 1; J:=0; whileJ<Lens LOOP J:=INSTR (p_str, P_delimiter, i); IFJ= 0 ThenJ:=Lens; Str:=SUBSTR (P_str, i); Str_split. EXTEND; Str_split (str_split.COUNT) := Str; IFI>=Lens Then EXIT; END IF; ELSE Str:=SUBSTR (P_str, I, J-i); I:=J+LENP; Str_split. EXTEND; Str_split (str_split.COUNT) := Str; END IF; ENDLOOP; forXinch 1.. Str_split.CountLoop Dbms_output.put_line (Str_split (x)); ENDLoop;END;
Oracle stored procedure split code implementation