Oracle Common Business Data Statistics SQL

--String Segmentation ExampleSELECTREGEXP_SUBSTR ('17,20,23','[^,]+',1, Level,'I') as STR     fromDUAL CONNECT by  Level <=LENGTH ('17,20,23')-LENGTH (Regexp_replace ('17,20,23',',',"'))+1; CREATE TABLETm_change (ID Number, Transit_znoVARCHAR2(Ten), SRCVARCHAR2( -), desVARCHAR2( -));CREATE TABLETemp_tm_change (ID Number, Transit_znoVARCHAR2(Ten), SRCVARCHAR2( -), desVARCHAR2( -));--Original DataSELECTT.*, ROWID fromTm_change t;--Output DataSELECTT.*  fromTemp_tm_change t;DELETE  fromTemp_tm_change;COMMIT;DECLARERecordCount Number;--number of record lines derived from a recordSumcount Number;--total number of barsBEGINSumcount:= 0; --sum   forRsinch(SELECTId,transit_zno, Src,des fromTm_change) LOOP RecordCount:= 0; --src        forSrcrsinch(SELECTRegexp_substr (RS.SRC,'[^,]+',1, Level,'I') as Str             fromDUAL CONNECT by  Level <=LENGTH (RS.SRC)-LENGTH (Regexp_replace (RS.SRC,',',"'))+1) LOOP--des          forDesrsinch(SELECTRegexp_substr (Rs.des,'[^,]+',1, Level,'I') as Str               fromDUAL CONNECT by  Level <=LENGTH (Rs.des)-LENGTH (Regexp_replace (Rs.des,',',"'))+1) LOOP RecordCount:=RecordCount+ 1; Sumcount:=Sumcount+ 1; INSERT  intoTemp_tm_change (Id,transit_zno,src,des)VALUES(Rs.id,rs.transit_zno,srcrs.Str, Desrs.Str); COMMIT; ENDLOOP; ENDLOOP; --dbms_output.put_line ('Insert a Record:id' ||Rs.id|| 'RecordCount --' ||recordCount); Buffer overflowENDLOOP; --dbms_output.put_line ('Create record Sumcount --' ||sumcount);END;SELECTTransit_zno,src,des fromTemp_tm_changeGROUP  byTransit_zno,src,des having COUNT(*)> 1 ; 

Oracle Common Business Data Statistics SQL