-- ------------------------------------------------------- -- Test Tables -- DROP TABLE SCOTT.TEST_SWIFT CASCADE CONSTRAINTS / CREATE TABLE SCOTT.TEST_SWIFT ( MSGBATCH CLOB ) LOB (MSGBATCH) STORE AS ( TABLESPACE USERS ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )) TABLESPACE USERS RESULT_CACHE (MODE DEFAULT) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING / DROP TABLE SCOTT.TEST_SWIFT_LINE CASCADE CONSTRAINTS / CREATE TABLE SCOTT.TEST_SWIFT_LINE ( REF VARCHAR2(16 BYTE), MSG# NUMBER(5), TAG VARCHAR2(10 BYTE), DATA VARCHAR2(4000 BYTE) ) TABLESPACE USERS RESULT_CACHE (MODE DEFAULT) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING / -- ------------------------------------------------------- -- Test Data -- INSERT INTO Test_Swift VALUES ( '102 :20:E9398582572312 :23:CREDIT :52A:SENDER Sender client :71A:SHA :21:TT/2031/4 :32B:INR75,00 :50K:/AC1 ACC1 holder :57A:RECEIVER Receiver Client :59:/AC2 ACC2 holder :70:Normal :26T:039 :77B:/DNUM/01 /DDATE/140702 :21:TT/2037/124 :32B:INR700,00 :50K:/Acc3 TOTAL FOOD :57A:RECEIVER Receiver Client :59:/Acc4 Acc4 holder :70:Method :26T:025 :77B:/DNUM/01 /DDATE/150705 :32A:140702INR775,00 :72:/NUM/5 /PRT/20' ); INSERT INTO Test_Swift VALUES ( '102 :20:5362/MPB :23:CREDIT :50K:CONSORTIA PENSION SCHEME FRIEDRICHSTRASSE, 27 8022-ZURICH :71A:OUR :36:1,6 :21:ABC/123 :32B:EUR1250, :59:/001161685134 JOHANN WILLEMS RUE JOSEPH II, 19 1040 BRUSSELS :70:PENSION PAYMENT SEPTEMBER 2003 :33B:CHF2000, :71G:EUR5, :21:ABC/124 :32B:EUR1875, :59:/510007547061 JOAN MILLS AVENUE LOUISE 213 1050 BRUSSELS :70:PENSION PAYMENT SEPTEMBER 2003 :33B:CHF3000, :71G:EUR5, :32A:030828EUR3135, :19:3125, :71G:EUR10, /PRT/20' ); INSERT INTO Test_Swift VALUES ( '102 :20:5362/MPB :23:CREDIT :50K:CONSORTIA PENSION SCHEME FRIEDRICHSTRASSE, 27 8022-ZURICH :71A:OUR :36:1,6 :21:ABC/123 :32B:EUR1250, :59:/001161685134 JOHANN WILLEMS RUE JOSEPH II, 19 1040 BRUSSELS :70:PENSION PAYMENT SEPTEMBER 2003 :33B:CHF2000, :71G:EUR5, :21:ABC/124 :32B:EUR1875, :59:/510007547061 JOAN MILLS AVENUE LOUISE 213 1050 BRUSSELS :70:PENSION PAYMENT SEPTEMBER 2003 :33B:CHF3000, :71G:EUR5, :32A:030828EUR3135, :19:3125, :71G:EUR10, /PRT/20' ); INSERT INTO Test_Swift VALUES ( '102 :20:5362/MPB :23:CREDIT :50K:CONSORTIA PENSION SCHEME FRIEDRICHSTRASSE, 27 8022-ZURICH :71A:OUR :36:1,6 :21:ABC/123 :32B:EUR1250, :59:/001161685134 JOHANN WILLEMS RUE JOSEPH II, 19 1040 BRUSSELS :70:PENSION PAYMENT SEPTEMBER 2003 :33B:CHF2000, :71G:EUR5, :21:ABC/124 :32B:EUR1875, :59:/510007547061 JOAN MILLS AVENUE LOUISE 213 1050 BRUSSELS :70:PENSION PAYMENT SEPTEMBER 2003 :33B:CHF3000, :71G:EUR5, :32A:030828EUR3135, :19:3125, :71G:EUR10, /PRT/20' ); COMMIT; -- ------------------------------------------------------- -- Procedure -- VAR Swiftrp REFCURSOR; DECLARE Wk0 CLOB; V_Seg VARCHAR2 ( 2000 ) := ''; V_Reference VARCHAR2 ( 16 ); V_Tag VARCHAR2 ( 10 ); V_Dat VARCHAR2 ( 4000 ); V_Sql VARCHAR2 ( 4000 ); V_Msg# NUMBER ( 5 ); V_Comma VARCHAR2 ( 2 ); TYPE Chr_Type IS TABLE OF VARCHAR ( 4000 ) INDEX BY PLS_INTEGER; TYPE Num_Type IS TABLE OF NUMBER ( 5 ) INDEX BY PLS_INTEGER; In_Msg Chr_Type; Msg_Num Num_Type; Msg_Dat Chr_Type; Msg_Tag Chr_Type; Tag_Flds Chr_Type; I PLS_INTEGER; J PLS_INTEGER; K PLS_INTEGER; L PLS_INTEGER; M PLS_INTEGER; N PLS_INTEGER; N_Tags PLS_INTEGER := 0; X PLS_INTEGER := 0; PROCEDURE Init_Arrays IS I PLS_INTEGER; BEGIN FOR I IN 1 .. Msg_Dat.COUNT LOOP Msg_Num ( I ) := 0; Msg_Dat ( I ) := ''; Msg_Tag ( I ) := ''; END LOOP; END; PROCEDURE Sort_Tags ( Tag_Flds IN OUT Chr_Type ) IS I PLS_INTEGER; K PLS_INTEGER; V_Tag VARCHAR2 ( 16 ); BEGIN FOR I IN 1 .. N_Tags - 1 LOOP FOR K IN I + 1 .. N_Tags - 1 LOOP IF Tag_Flds ( I ) > Tag_Flds ( K ) THEN V_Tag := Tag_Flds ( K ); Tag_Flds ( K ) := Tag_Flds ( I ); Tag_Flds ( I ) := V_Tag; END IF; END LOOP; END LOOP; END; PROCEDURE Add_Tag ( P_Tag VARCHAR2 ) IS I PLS_INTEGER; K PLS_INTEGER; BEGIN K := 0; IF N_Tags = 0 THEN N_Tags := 1; Tag_Flds ( N_Tags ) := P_Tag; ELSE K := 0; FOR I IN 1 .. Tag_Flds.COUNT LOOP IF P_Tag = Tag_Flds ( I ) THEN K := 0; GOTO Endloop; END IF; K := I; END LOOP; N_Tags := N_Tags + 1; Tag_Flds ( N_Tags ) := P_Tag; END IF; <> NULL; END; PROCEDURE Write_Msg ( P_Msg# Num_Type ) IS BEGIN DBMS_OUTPUT.Put_Line ( '========== TAGS ' || Tag_Flds.COUNT ); FOR I IN 1 .. Tag_Flds.COUNT LOOP DBMS_OUTPUT.Put_Line ( TO_CHAR ( I, '000.' ) || Tag_Flds ( I ) ); END LOOP; DBMS_OUTPUT.Put_Line ( '========== Message Lines ' || Msg_Tag.COUNT ); FOR I IN 1 .. Msg_Tag.COUNT LOOP DBMS_OUTPUT.Put_Line ( TO_CHAR ( I, '000.' ) || TO_CHAR ( Msg_Num ( I ), 'FM00.S' ) || Msg_Tag ( I ) || ' = ' || Msg_Dat ( I ) || '<' ); INSERT INTO Test_Swift_Line VALUES ( V_Reference , Msg_Num ( I ) , Msg_Tag ( I ) , Msg_Dat ( I ) ); END LOOP; COMMIT; END; PROCEDURE Coalesce_Msg ( P_In_Arr Chr_Type ) IS BEGIN N := 0; M := 0; J := 0; V_Seg := ''; V_Msg# := 0; -- skip header WHILE 1 = 1 LOOP J := J + 1; IF SUBSTR ( P_In_Arr ( J ), 1, 4 ) = ':20:' THEN V_Reference := SUBSTR ( P_In_Arr ( J ), 5 ); V_Msg# := 0; END IF; EXIT WHEN SUBSTR ( P_In_Arr ( J ), 1, 4 ) = ':21:'; END LOOP; V_Seg := ''; FOR I IN J .. P_In_Arr.COUNT LOOP IF SUBSTR ( P_In_Arr ( I ), 1, 1 ) = ':' THEN IF LENGTH ( V_Seg ) >= 0 THEN L := INSTR ( V_Seg, ':', 2 ) - 1; V_Tag := 'F' || SUBSTR ( V_Seg, 2, L - 1 ); V_Dat := SUBSTR ( V_Seg, L + 2 ); M := M + 1; Msg_Num ( M ) := V_Msg#; Msg_Tag ( M ) := V_Tag; Msg_Dat ( M ) := V_Dat; Add_Tag ( V_Tag ); END IF; IF SUBSTR ( P_In_Arr ( I ), 1, 4 ) = ':21:' THEN V_Msg# := V_Msg# + 1; END IF; V_Seg := P_In_Arr ( I ); ELSE V_Seg := V_Seg || '; ' || P_In_Arr ( I ); END IF; END LOOP; END; BEGIN DBMS_OUTPUT.Enable ( 1000000 ); EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST_SWIFT_LINE'; Init_Arrays; FOR B IN ( SELECT * FROM Test_Swift ) LOOP Wk0 := B.Msgbatch || CHR ( 10 ); N := 0; I := 1; L := 0; DBMS_OUTPUT.Put_Line ( '==============' || CHR ( 10 ) || Wk0 || '==============' ); M := 0; WHILE ( 1 = 1 ) LOOP K := INSTR ( Wk0, CHR ( 10 ) ); M := M + 1; EXIT WHEN M > 100; IF K > 2 THEN V_Seg := SUBSTR ( Wk0, 1, K - 1 ); N := N + 1; In_Msg ( N ) := V_Seg; Wk0 := SUBSTR ( Wk0 || CHR ( 10 ), K + 1 ); K := LENGTH ( Wk0 ); ELSE Wk0 := ''; EXIT; END IF; END LOOP; Coalesce_Msg ( In_Msg ); Write_Msg ( Msg_Num ); END LOOP; V_Seg := ''; V_Comma := ''; FOR I IN 1 .. Tag_Flds.COUNT LOOP V_Seg := V_Seg || V_Comma || '''' || Tag_Flds ( I ) || ''''; V_Comma := ','; END LOOP; COMMIT; V_Sql := ' SELECT * FROM ( SELECT REF, Msg#, Tag, Data FROM Test_Swift_Line ORDER BY REF, Msg#, Tag) PIVOT (MAX ( Data ) FOR Tag IN (' || V_Seg || '))'; DBMS_OUTPUT.Put_Line ( V_Sql ); OPEN :Swiftrp FOR V_Sql; END; / PRINT Swiftrp