I have two clob columns in my table KEY & VAL.
Key column data:
ACTION|COCODE|SNCODE|SPCODE|PARAMETER_ID|PARAMETER_VALUE|PARAMETER_ID|PARAMETER_VALUE|SNCODE|SPCODE|SNCODE|SPCODE|PARAMETER_ID|PARAMETER_VALUE
VAL column data:
Add|CONTR0019209052|123|456|1|abc|111|zzz|222|999|333|888|666|tttt
I want to convert this data in JSON format like this:
"my": [
{"action": "Add",
"SNCODE|SPCODE": "123|456",
"parameter": [
{
"PARAMETER_ID":"1" ,
"PARAMETER_VALUE":"abc"
},
{
"PARAMETER_ID":"111" ,
"PARAMETER_VALUE":"zzz"
}
]
},
{
"action": "Add",
"SNCODE|SPCODE": "222|999"
},
{
"action": "Add",
"SNCODE|SPCODE": "333|888",
"parameter": [
{
"PARAMETER_ID":"666" ,
"PARAMETER_VALUE":"tttt"
}
]
}
]
How I can achieve this SNCODE &SPCODE,parameter can come number of times.
OK, firstly check the post here
https://asktom.oracle.com/pls/apex/asktom.search?tag=parsing-a-clob-field-which-contains-csv-data which is a simple demonstration of how to parse a clob containing CSV data.
Then I took the resultant GTT and parsed it with some simple string processing
SQL>
SQL> create table T ( id int, c1 clob, c2 clob );
Table created.
SQL>
SQL> insert into t
2 values (1,
3 'ACTION|COCODE|SNCODE|SPCODE|PARAMETER_ID|PARAMETER_VALUE|PARAMETER_ID|PARAMETER_VALUE|SNCODE|SPCODE|SNCODE|SPCODE|PARAMETER_ID|PARAMETER_VALUE',
4 'Add|CONTR0019209052|123|456|1|abc|111|zzz|222|999|333|888|666|tttt');
1 row created.
SQL>
SQL> create global temporary table GTT (id int, seq int, col varchar2(100), val varchar2(100) )
2 on commit preserve rows;
Table created.
SQL>
SQL> create sequence seq;
Sequence created.
SQL>
SQL> set serverout on
SQL> declare
2 l_big_chunk varchar2(32767);
3 l_leftover varchar2(200);
4 l_offset pls_integer;
5 l_len pls_integer;
6
7 type string_tab is table of varchar2(100) index by pls_integer;
8 l_cols string_tab;
9 l_vals string_tab;
10
11 l_delim pls_integer;
12 begin
13 for i in ( select * from t )
14 loop
15
16 --
17 -- c1 first
18 --
19 l_offset := 1;
20 loop
21 l_big_chunk := dbms_lob.substr(i.c1,32700,l_offset);
22 l_len := length(l_big_chunk);
23 l_offset := l_offset + l_len;
24 l_big_chunk := l_leftover || l_big_chunk;
25
26 loop
27 l_delim := instr(l_big_chunk,'|');
28 exit when l_delim = 0 or l_big_chunk is null;
29 l_cols(l_cols.count+1) := substr(l_big_chunk,1,l_delim-1);
30 l_big_chunk := substr(l_big_chunk,l_delim+1);
31 end loop;
32 l_leftover := l_big_chunk;
33
34 exit when l_len < 32700;
35 end loop;
36 if l_big_chunk is not null then
37 l_cols(l_cols.count+1) := l_big_chunk;
38 end if;
39 --
40 -- then same for c2
41 --
42
43 l_offset := 1;
44 l_leftover := null;
45 loop
46 l_big_chunk := dbms_lob.substr(i.c2,32700,l_offset);
47 l_len := length(l_big_chunk);
48 l_offset := l_offset + l_len;
49 l_big_chunk := l_leftover || l_big_chunk;
50
51 loop
52 l_delim := instr(l_big_chunk,'|');
53 exit when l_delim = 0 or l_big_chunk is null;
54 l_vals(l_vals.count+1) := substr(l_big_chunk,1,l_delim-1);
55 l_big_chunk := substr(l_big_chunk,l_delim+1);
56 end loop;
57 l_leftover := l_big_chunk;
58
59 exit when l_len < 32700;
60 end loop;
61 if l_big_chunk is not null then
62 l_vals(l_vals.count+1) := l_big_chunk;
63 end if;
64
65 forall g in 1 .. l_cols.count
66 insert into gtt values (i.id,seq.nextval,l_cols(g), l_vals(g) ) ;
67 l_cols.delete;
68 l_vals.delete;
69 end loop;
70 end;
71 /
PL/SQL procedure successfully completed.
SQL>
SQL> col col format a40
SQL> col val format a40
SQL> set lines 150
SQL> set long 5000
SQL> set longchunksize 1000
SQL> select * from gtt;
ID SEQ COL VAL
---------- ---------- ---------------------------------------- ----------------------------------------
1 1 ACTION Add
1 2 COCODE CONTR0019209052
1 3 SNCODE 123
1 4 SPCODE 456
1 5 PARAMETER_ID 1
1 6 PARAMETER_VALUE abc
1 7 PARAMETER_ID 111
1 8 PARAMETER_VALUE zzz
1 9 SNCODE 222
1 10 SPCODE 999
1 11 SNCODE 333
1 12 SPCODE 888
1 13 PARAMETER_ID 666
1 14 PARAMETER_VALUE tttt
14 rows selected.
SQL>
SQL> set serverout on
SQL> declare
2 c clob;
3 sncode varchar2(100);
4 eol varchar2(1) := chr(10);
5 param varchar2(100);
6 begin
7 for i in ( select g.*,
8 row_number() over ( partition by id order by seq) r,
9 count(*) over ( partition by id ) as cnt
10 from gtt g
11 order by 1,2 )
12 loop
13 if i.col = 'ACTION' then
14 c := '"my": ['||eol||
15 '{"action": "'||i.val||'",'||eol;
16 elsif i.col = 'SNCODE' then
17 sncode := i.val;
18 elsif i.col = 'SPCODE' then
19 c := c ||'"SNCODE|SPCODE": "'||sncode||'|'||i.val||'",'||eol||
20 '"parameter": ['||eol;
21 elsif i.col = 'PARAMETER_ID' then
22 param := i.val;
23 elsif i.col = 'PARAMETER_VALUE' then
24 c := c || '{'||eol||
25 '"PARAMETER_ID":"'||param||'",'||eol||
26 '"PARAMETER_VALUE":"'||i.val||'"'||eol||
27 '}'||case when i.r < i.cnt then ',' else ']}]' end||eol;
28 end if;
29
30 if i.r = i.cnt then
31 dbms_output.put_line(c);
32 end if;
33 end loop;
34 end;
35 /
"my": [
{"action": "Add",
"SNCODE|SPCODE": "123|456",
"parameter":
[
{
"PARAMETER_ID":"1",
"PARAMETER_VALUE":"abc"
},
{
"PARAMETER_ID":"111",
"PARAMETER_VALUE":"zzz"
},
"SNCODE|SPCODE": "222|999",
"parameter":
[
"SNCODE|SPCODE": "333|888",
"parameter": [
{
"PARAMETER_ID":"666",
"PARAMETER_VALUE":"tttt"
}]}]
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>