1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
| CREATE TABLE t_lign
(
id number(10) PRIMARY key,
lign CLOB
);
INSERT INTO t_lign
select 1, q'~{"SALA__NOM":"IRONE","SALA__PRENOM":"MAN","NDF__DATE_CREA":"11/02/2022 16:25:00","FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20,"FRAI__TTC":20}~'
from dual union all
select 2, q'~{"SALA__NOM":"MONSIEUR","SALA__PRENOM":"PARFAIT","FRAI__DATE_DEPL":"26/08/2021 00:00:00","FRAI__TTC":38.8}~'
from dual union all
select 3, q'~{"SALA__NOM":"DUJARDIN","SALA__PRENOM":"JEANETTE","NDF__DATE_CREA":"10/02/2022 19:08:00","NDF__DATE_BASC":"10/02/2022 19:25:00","FRAI__TTC":203}~'
from dual
;
CREATE TYPE lignresul_t AS OBJECT (
id number(10),
KEY varchar2(64),
value varchar2(64)
);
/
CREATE TYPE lignresul_tt AS TABLE OF lignresul_t ;
/
CREATE OR REPLACE FUNCTION convert_lign_json
RETURN lignresul_tt PIPELINED
IS
v_id NUMBER(10);
v_key VARCHAR2(64);
v_value VARCHAR2(64);
v_result lignresul_tt;
BEGIN
FOR rec IN (
with guides(id, guide) as (
select id, json_dataguide(lign,dbms_json.format_flat, DBMS_JSON.PRETTY)
from t_lign
group by id
)
select
'SELECT lignresul_t(' || id || ', ''' || REPLACE(k,'$.','') || ''', json_value(lign, ''' || k || ''') ) FROM t_lign WHERE id = ' || id AS script
from guides,
json_table(
guide,
'$[*]'
columns (
k PATH '$."o:path"'
)
)
WHERE k <> '$'
)
LOOP
EXECUTE IMMEDIATE rec.script BULK COLLECT INTO v_result ;
FOR i IN v_result.first .. v_result.last
LOOP
PIPE ROW(v_result(i));
END LOOP;
END LOOP
;
END ;
/
SELECT * FROM CONVERT_LIGN_JSON() ; |
