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 Go to next message
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 #639331 is a reply to message #639328] Mon, 06 July 2015 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
you can see there are two sets of data starting from :21: to :77B:,


If you say so.

Quote:
Manual insertion is done here but the record sets are dynamic


In which way?
What is dynamic and what is not?

Re: Swift message insert script [message #639358 is a reply to message #639331] Mon, 06 July 2015 07:50 Go to previous messageGo to next message
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 #639364 is a reply to message #639358] Mon, 06 July 2015 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This does not answer my questions.
What is a record? Definition? Specification?
You said "starting from :21: to :77B:," what else?

icon12.gif  Re: Swift message insert script [message #639372 is a reply to message #639328] Mon, 06 July 2015 09:14 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Your test_swift table may only work for messages type 102, what about the rest of the messages where the fields may be different?

SWIFT documents for transmission ISO 15022 Data Field Dictionary


[Updated on: Mon, 06 July 2015 09:16]

Report message to a moderator

Re: Swift message insert script [message #639399 is a reply to message #639364] Mon, 06 July 2015 14:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

icon12.gif  Re: Swift message insert script [message #639457 is a reply to message #639399] Tue, 07 July 2015 15:19 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
In order to achieve the above, you may need to code some kind of parser routine.
These instructions may help you: Anatomy of a SWIFT message
Re: Swift message insert script [message #639559 is a reply to message #639437] Thu, 09 July 2015 16:34 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
You are lucky you do not have to validate the data.
I came up with a solution, check it out (attached).
Good Luck!
Razz

Re: Swift message insert script [message #639609 is a reply to message #639559] Sun, 12 July 2015 02:22 Go to previous message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member

Thanks LKBrwn

I will try the attached procedure..
Previous Topic: Default Value
Next Topic: Variable in PLSQL
Goto Forum:
  


Current Time: Thu Mar 28 20:27:32 CDT 2024