-- -------------------------------------------------------
-- 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