Hi, Tom!
I need some advice in what is the best way to solve the following task in PL/SQL.
I have the following link
https://wiki.xmldation.com/Support/EPC/List_of_SEPA_countries I need to get IBAN-s (the third column) from this site and compare it with the appropriate column (type is Varchar2) in the table. If they are the same, then, return true.
Can you give me some tips, how can I do this task or where to start from? Do I need to use XML parser; or need to save data into file and read data from it?
Thank you in advance.
Jeli.
I took a subsection of the html, but this should get you started
SQL> drop table t purge;
Table dropped.
SQL> create table t ( c clob );
Table created.
SQL>
SQL> set define off
SQL> begin
2 insert into t values (
3 '/*]]>*/</script>
4 </p>
5 <div id="section_1"><span id="List_of_SEPA_countries_or_territories_and_their_ISO_country_and_currency_codes"></span><h5 class="editable">List of SEPA countries or territories and their ISO country and currency codes </h5>
6 <div>SEPA consists of the 27 EU countries (including the Euro 16 countries) and the additional 3 EEA </div>
7 <div>(European Economic Area) countries. </div>
8 <div> </div>
9 <div>In SEPA messages, countries are denominated by their ISO country codes contained in the BICs </div>
10 <div>and the IBANs. Amounts are expressed in the currencies of these countries, denominated by their </div>
11 <div>ISO currency codes. The list presents the name of the countries or territories with the corresponding </div>
12 <div>ISO country and currency codes. </div>
13 <div> </div>
14 <table border="0" cellpadding="0" cellspacing="0" width="512"> <colgroup> <col /> <col span="3" /> </colgroup> <tbody> <tr> <td height="17"><strong>Country/territory </strong></td> <td><strong>BIC </strong></td> <td><strong>IBAN </strong></td> <td><strong>Currency code </strong>'||
15 '</td> </tr> <tr> <td height="17">Åland Islands </td> <td>FI </td> <td>FI </td> <td>EUR </td> </tr> <tr> <td height="17">Austria </td> <td>AT </td> <td>AT </td> <td>EUR </td> </tr> <tr> <td height="17">Belgium </td> <td>BE </td> <td>BE </td> '||
16 '<td>EUR </td> </tr> <tr> <td height="17">Bulgaria </td> <td>BG </td> <td>BG </td> <td>BGN </td> </tr> <tr> <td height="17">Canary Islands </td> <td>ES </td> <td>ES </td> <td>EUR </td> </tr> <tr> <td height="17">Croatia</td> <td>HR</td> <td>HR</td> <td>HRK</td> </tr> <tr> '||
17 '<td height="17">Cyprus </td> <td>CY </td> <td>CY </td> <td>EUR </td> </tr> <tr> <td height="17">Czech Republic </td> <td>CZ </td> <td>CZ </td> <td>CZK </td> </tr> <tr> <td height="17">Denmark </td> <td>DK </td> <td>DK </td> <td>DKK </td> </tr> <tr> <td height="17">'||
18 'Estonia </td> <td>EE </td> <td>EE </td> <td>EEK </td> </tr> <tr> <td height="17">Finland </td> <td>FI </td> <td>FI </td> <td>EUR </td> </tr> <tr> <td height="17">France </td> <td>FR </td> <td>FR </td> <td>EUR </td> </tr> <tr> <td height="17">'||
19 'French Guiana </td> <td>GF </td> <td>FR </td> <td>EUR </td> </tr> <tr> <td height="17">Germany </td> <td>DE </td> <td>DE </td> <td>EUR </td> </tr> <tr> <td height="17">Gibraltar </td> <td>GI </td> <td>GI </td> <td>GIP </td> </tr> <tr> <td height="17">'||
20 'Greece </td> <td>GR </td> <td>GR </td> <td>EUR </td> </tr> <tr> <td height="17">Guadeloupe </td> <td>GP </td> <td>FR </td> <td>EUR </td> </tr> <tr> <td height="17">Hungary </td> <td>HU </td> <td>HU </td> <td>HUF </td> </tr> <tr> <td height="17">Iceland '||
21 '</td> <td>IS </td> <td>IS </td> <td>ISK </td> </tr> <tr> <td height="17">Ireland </td> <td>IE </td> <td>IE </td> <td>EUR </td> </tr> <tr> <td height="17">Italy </td> <td>IT </td> <td>IT </td> <td>EUR </td> </tr> <tr> <td height="17">Latvia </td> <td>LV </td>'||
22 ' <td>LV </td> <td>EUR</td> </tr> <tr> <td height="17">Liechtenstein </td> <td>LI </td> <td>LI </td> <td>CHF </td> </tr> <tr> <td height="17">Lithuania </td> <td>LT </td> <td>LT </td> <td>LTL </td> </tr> <tr> <td height="17">Luxembourg </td> <td>LU </td> <td>LU </td>'||
23 ' <td>EUR </td> </tr> <tr> <td height="17">Malta </td> <td>MT </td> <td>MT </td> <td>EUR </td> </tr> <tr> <td height="17">Martinique </td> <td>MQ </td> <td>FR </td> <td>EUR </td> </tr> <tr> <td height="17">Mayotte </td> <td>YT </td> <td>FR </td> <td>EUR </td> '||
24 '</tr> <tr> <td height="17">Monaco </td> <td>MC </td> <td>MC </td> <td>EUR </td> </tr> <tr> <td height="17">Netherlands </td> <td>NL </td> <td>NL </td> <td>EUR </td> </tr> <tr> <td height="17">Norway </td> <td>NO </td> <td>NO </td> <td>NOK </td> </tr> <tr> '||
25 '<td height="17">Poland </td> <td>PL </td> <td>PL </td> <td>PLN </td> </tr> <tr> <td height="17">Portugal </td> <td>PT </td> <td>PT </td> <td>EUR </td> </tr> <tr> <td height="17">Réunion </td> <td>RE </td> <td>FR </td> <td>EUR </td> </tr> <tr> '||
26 '<td height="17">Romania </td> <td>RO </td> <td>RO </td> <td>RON </td> </tr> <tr> <td height="17">Saint Barthélemy </td> <td>BL </td> <td>FR </td> <td>EUR </td> </tr> <tr> <td height="17">Saint Martin (French part) </td> <td>MF </td> <td>FR </td> <td>EUR </td> '||
27 '</tr> <tr> <td height="17">Saint Pierre and Miquelon</td> <td>PM </td> <td>FR </td> <td>EUR </td> </tr> <tr> <td height="17">Slovakia </td> <td>SK </td> <td>SK </td> <td>EUR </td> </tr> <tr> <td height="17">Slovenia </td> <td>SI </td> <td>SI </td> <td>EUR </td> </tr> <tr> '||
28 '<td height="17">Spain </td> <td>ES </td> <td>ES </td> <td>EUR </td> </tr> <tr> <td height="17">Sweden </td> <td>SE </td> <td>SE </td> <td>SEK </td> </tr> <tr> <td height="17">Switzerland </td> <td>CH </td> <td>CH </td> <td>CHF </td> </tr> <tr> <td height="17">'||
29 'United Kingdom </td> <td>GB </td> <td>GB </td> <td>GBP </td> </tr> </tbody></table>');
30 end;
31 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> set define off
SQL> set serverout on
SQL> declare
2 c1 clob;
3 l_list varchar2(32000);
4 pos pls_integer;
5 endpos pls_integer;
6
7 type str_array is table of varchar2(100) index by pls_integer;
8 l_country str_array;
9 l_bic str_array;
10 l_iban str_array;
11 l_curr str_array;
12
13 l_row_count pls_integer := 0;
14 l_col_count pls_integer := 0;
15 idx pls_integer;
16 begin
17 select c into c1 from t;
18 pos := dbms_lob.instr(c1,'Country/territory');
19 pos := dbms_lob.instr(c1,'<tr>',pos-50);
20 endpos := dbms_lob.instr(c1,'</table>',pos);
21
22 l_list := dbms_lob.substr(c1,endpos-pos,pos);
23 l_list := ltrim(rtrim(l_list));
24
25 l_list := replace(replace(replace(l_list,'<strong>'),'</strong>'),' ');
26
27 loop
28 exit when l_list = '</tbody>';
29
30 if substr(l_list,1,5) in ('</td>','</tr>') then
31 l_list := ltrim(substr(l_list,6));
32 continue;
33 end if;
34
35 if substr(l_list,1,4) = '<tr>' then
36 l_row_count := l_row_count + 1;
37 l_list := ltrim(substr(l_list,5));
38 l_col_count := 0;
39 end if;
40
41 if substr(l_list,1,3) = '<td' then
42 l_col_count := l_col_count + 1;
43 l_list := ltrim(substr(l_list,instr(l_list,'>')+1));
44 end if;
45
46 idx := instr(l_list,'<');
47
48 if l_col_count = 1 then
49 l_country(l_row_count) := substr(l_list,1,idx-1);
50 elsif l_col_count = 2 then
51 l_bic(l_row_count) := substr(l_list,1,idx-1);
52 elsif l_col_count = 3 then
53 l_iban(l_row_count) := substr(l_list,1,idx-1);
54 elsif l_col_count = 4 then
55 l_curr(l_row_count) := substr(l_list,1,idx-1);
56 end if;
57
58 l_list := ltrim(substr(l_list,idx));
59
60 end loop;
61
62 for i in 2 .. l_row_count loop
63 dbms_output.put_line(
64 rpad(l_country(i),30)||
65 rpad(l_bic(i),10)||
66 rpad(l_iban(i),10)||
67 rpad(l_curr(i),10)
68 );
69 end loop;
70
71 end;
72 /
Åland Islands FI FI EUR
Austria AT AT EUR
Belgium BE BE EUR
Bulgaria BG BG BGN
Canary Islands ES ES EUR
Croatia HR HR HRK
Cyprus CY CY EUR
Czech Republic CZ CZ CZK
Denmark DK DK DKK
Estonia EE EE EEK
Finland FI FI EUR
France FR FR EUR
French Guiana GF FR EUR
Germany DE DE EUR
Gibraltar GI GI GIP
Greece GR GR EUR
Guadeloupe GP FR EUR
Hungary HU HU HUF
Iceland IS IS ISK
Ireland IE IE EUR
Italy IT IT EUR
Latvia LV LV EUR
Liechtenstein LI LI CHF
Lithuania LT LT LTL
Luxembourg LU LU EUR
Malta MT MT EUR
Martinique MQ FR EUR
Mayotte YT FR EUR
Monaco MC MC EUR
Netherlands NL NL EUR
Norway NO NO NOK
Poland PL PL PLN
Portugal PT PT EUR
Réunion RE FR EUR
Romania RO RO RON
Saint Barthélemy BL FR EUR
Saint Martin (French part) MF FR EUR
Saint Pierre and Miquelon PM FR EUR
Slovakia SK SK EUR
Slovenia SI SI EUR
Spain ES ES EUR
Sweden SE SE SEK
Switzerland CH CH CHF
United Kingdom GB GB GBP
PL/SQL procedure successfully completed.
SQL>