Home » SQL & PL/SQL » SQL & PL/SQL » Swift message insert script (oracle 11g)
Swift message insert script [message #639328] |
Mon, 06 July 2015 05:51 |
Nusrat
Messages: 38 Registered: June 2007 Location: Mumbai(India)
|
Member |
|
|
Hi
I need a help in data insertion of a swift message in multiple line.
create table test_swift
(block4 clob
);
insert into test_swift
values
(
'<batch><msg_subtype>102</msg_subtype><body>
: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</body><sign></sign></batch>'
);
commit;
you can see there are two sets of data starting from :21: to :77B:, so i wanted to insert two sets of record in another table.(example is taken for two sets of record and it can be more than two sets)
drop table test_swift_line;
create table test_swift_line
(
id number(5),
tdate date,
f20 varchar2(20),
f23 char(10),
f52a char(20),
f52a_dtl varchar2(50),
f71a char(3),
f21 varchar2(20),
f32b varchar2(20),
f50k varchar2(20),
f50k_dtl varchar2(50),
f57a char(20),
f57_dtl varchar2(50),
f59 varchar2(20),
f59_dl varchar2(50),
f70 varchar2(50),
f26t varchar(10),
f77b varchar2(50),
total_amt number
);
Insert into TEST_SWIFT_LINE
(ID, TDATE, F20, F23, F52A, F52A_DTL, F71A, F21, F32B, F50K, F50K_DTL, F57A, F57_DTL, F59, F59_DL, F70, F26T, F77B, TOTAL_AMT)
Values
(111, TO_DATE('07/06/2015 14:07:07', 'MM/DD/YYYY HH24:MI:SS'), 'E9398582572312', 'CREDIT ', 'SENDER ', 'Sender client', 'SHA', 'TT/2031/4', 'INR75,00', 'AC1', 'ACC1 holder', 'RECEIVER ', 'Receiver Client', 'AC2', 'ACC2 holder', 'Normal', '039', '/DNUM/01
/DDATE/140702', 775);
Insert into TEST_SWIFT_LINE
(ID, TDATE, F20, F23, F52A, F52A_DTL, F71A, F21, F32B, F50K, F50K_DTL, F57A, F57_DTL, F59, F59_DL, F70, F26T, F77B, TOTAL_AMT)
Values
(111, TO_DATE('07/06/2015 14:37:03', 'MM/DD/YYYY HH24:MI:SS'), 'E9398582572312', 'CREDIT ', 'SENDER ', 'Sender client', 'SHA', 'TT/2037/124', 'INR700,00', 'Acc3', 'TOTAL FOOD', 'RECEIVER ', 'Receiver Client', 'Acc4', 'Acc4 holder', 'Method', '025', '/DNUM/01
/DDATE/140702', 775);
COMMIT;
Manual insertion is done here but the record sets are dynamic so multiple entry will happen.
Thanks in advance.
Nusrat
|
|
|
|
Re: Swift message insert script [message #639358 is a reply to message #639331] |
Mon, 06 July 2015 07:50 |
Nusrat
Messages: 38 Registered: June 2007 Location: Mumbai(India)
|
Member |
|
|
Hi
Here i have taken example of two record sets but it could be more record sets in my core system which generates the CLOB data which i need to insert in table "test_swift_line'.IF my core system is generating 10 record sets then 10 line of entry is required in the test_swift_line table and i do not want to insert it one by one record set.
: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
:21:TT/2037/125
:32B:INR900,00
:50K:/Acc6
TOTAL FOOD
:57A:RECEIVER
Receiver Client
:59:/Acc8
Acc4 holder
:70:Method3
:26T:001
:77B:/DNUM/01
/DDATE/150705
and so on .......
Thanks
Nusrat
[Updated on: Mon, 06 July 2015 08:02] Report message to a moderator
|
|
|
|
|
Re: Swift message insert script [message #639399 is a reply to message #639364] |
Mon, 06 July 2015 14:23 |
Nusrat
Messages: 38 Registered: June 2007 Location: Mumbai(India)
|
Member |
|
|
Hi
One record means each line is a table column value and below starting from tag :21: to tag 77B is one record in different columns.
It is a Swift message MT102 format and i want to insert these below information in a table test_swift_line and other details in original message is constant data.
-------------first record set-----------
: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
---------------------
---------second record set-----------
: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
----------------------------
Here two records are given and i need the insert procedure in below table format.
select f20,f23,f20,f52a,f21,f32b,f50k,f59,f70,f26t,f77b from test_swift_line;
F20 F23 F52A F21 F32B F50K F59 F70 F26T F77B
E9398582572312 CREDIT SENDER TT/2031/4 INR75,00 AC1 AC2 Normal 039 /DNUM/01 /DDATE/140702
E9398582572312 CREDIT SENDER TT/2037/124 INR700,00 Acc3 Acc4 Method 025 /DNUM/01 /DDATE/140702
Thanks
Nusrat
|
|
|
Re: Swift message insert script [message #639403 is a reply to message #639372] |
Mon, 06 July 2015 14:31 |
Nusrat
Messages: 38 Registered: June 2007 Location: Mumbai(India)
|
Member |
|
|
Hi LKBrwn
Yes i am only looking for MT102 message format (Multi Credit). I have created procedure for MT103 (single credit) and here simple insert script is enough but in MT102 is having multiple credit instruction in one xml file and i don't know how many numbers of record will be available in one single xml file.
Nusrat
|
|
|
Re: Swift message insert script [message #639437 is a reply to message #639399] |
Tue, 07 July 2015 02:57 |
Nusrat
Messages: 38 Registered: June 2007 Location: Mumbai(India)
|
Member |
|
|
Hi Michel
I will make it little simple ,
Let's say i have a xml data in a table column
create table test_swift
(block4 clob
);
insert into test_swift
values
(
'<batch><msg_subtype>102</msg_subtype>
<body>:1:555
:2:SENDER
:3:11
:4:INR75,00
:5:/AC1
:6:RECEIVER
:7:/AC2
:8:/DNUM/01
/DDATE/140702
:3:22
:4:INR700,00
:5:/Acc3
:6:RECEIVER
:7:/Acc4
:8:/DNUM/02
/DDATE/140702
:9:140702INR775,00
:10:/NUM/5
/PRT/20</body><sign></sign></batch>'
);
commit;
Here each tag :1: ,:2:, :3: .... represents a table column in test_line table.
I want to use procedure to insert above data in another table "test_line" like below.
create table test_line
(
t1 varchar2(10),
t2 varchar2(10),
t3 varchar2(20),
t4 varchar2 (20),
t5 varchar2(10),
t6 varchar2(10),
t7 varchar2(20),
t8 varchar2(20),
t9 varchar2(50),
t10 varchar2(50)
)
T1 T2 T3 T4 T5 T6 T7 T8 T9 T10
555 SENDER 22 INR700,00 Acc3 RECEIVER Acc4 /DNUM/02/DDATE/140 140702INR775,00 /NUM/5/PRT/20
555 SENDER 11 INR75,00 AC1 RECEIVER AC2 /DNUM/01/DDATE/140 140702INR775,00 /NUM/5/PRT/20
Thanks
Nusrat
[Updated on: Tue, 07 July 2015 07:30] Report message to a moderator
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 20:27:32 CDT 2024
|