Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table TEST_TABLE
(id number,
importdata clob);  
BEGIN
insert into TEST_TABLE values (100,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"GEO_METHOD","Value":"96"},{"Name":"HPRCSN","Value":2.7676}]}');
insert into TEST_TABLE values (101,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"HPRCSN","Value":3.04}]}');
insert into TEST_TABLE values (102,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"GEO_METHOD","Value":"96"},{"Name":"HPRCSN","Value":77.1814}]}');
insert into TEST_TABLE values (103,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"GEO_METHOD","Value":"-1"},{"Name":"HPRCSN","Value":3.1121}]}');
insert into TEST_TABLE values (105,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"69804"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"} ');
insert into TEST_TABLE values (106,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"73576"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"}]} ');
insert into TEST_TABLE values (107,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"73589"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"}]} ');
insert into TEST_TABLE values (108,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"74015"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"}]} ');
END;
/
1 rows affected

select id,
       classid,
       name,
       value
from   TEST_TABLE t1
       CROSS APPLY JSON_TABLE(
         t1.importdata,
         '$'
         COLUMNS (
           classid NUMBER PATH '$.ClassId',
           NESTED PATH '$.Attributes[*]' COLUMNS (
             name  VARCHAR2(20) PATH '$.Name',
             value VARCHAR2(20) PATH '$.Value'
           )
         )
       );


ID
CLASSID
NAME
VALUE


100
30074
TYPE-SPEC
SJ;3;1

100
30074
HREF
-1

100
30074
HMETHOD
96

100
30074
GEO_METHOD
96

100
30074
HPRCSN
2.7676

101
30074
TYPE-SPEC
SJ;3;1

101
30074
HREF
-1

101
30074
HMETHOD
96

101
30074
HPRCSN
3.04

102
30074
TYPE-SPEC
SJ;3;1

102
30074
HREF
-1

102
30074
HMETHOD
96

102
30074
GEO_METHOD
96

102
30074
HPRCSN
77.1814

103
30074
TYPE-SPEC
SJ;3;1

103
30074
HREF
-1

103
30074
HMETHOD
96

103
30074
GEO_METHOD
-1

103
30074
HPRCSN
3.1121

105
32000
ID
69804

105
32000
HREF
-1

105
32000
HPRCSN
5

106
32000
ID
73576

106
32000
HREF
-1

106
32000
HPRCSN
5

107
32000
ID
73589

107
32000
HREF
-1

107
32000
HPRCSN
5

108
32000
ID
74015

108
32000
HREF
-1

108
32000
HPRCSN
5


SELECT *
FROM   (
  select id,
         classid,
         name,
         value
  from   TEST_TABLE t1
         CROSS APPLY JSON_TABLE(
           t1.importdata,
           '$'
           COLUMNS (
             classid NUMBER PATH '$.ClassId',
             NESTED PATH '$.Attributes[*]' COLUMNS (
               name  VARCHAR2(20) PATH '$.Name',
               value VARCHAR2(20) PATH '$.Value'
             )
           )
         ) j
)
PIVOT (
  MAX(value) FOR name IN (
    'ID' AS idvalue,
    'HREF' AS href,
    'GEO_METHOD' AS geomethod,
    'HPRCSN' AS hprcsn,
    'HMETHOD' AS hmethod
  )
);


ID
CLASSID
IDVALUE
HREF
GEOMETHOD
HPRCSN
HMETHOD


100
30074
null
-1
96
2.7676
96

107
32000
73589
-1
null
5
null

108
32000
74015
-1
null
5
null

101
30074
null
-1
null
3.04
96

106
32000
73576
-1
null
5
null

103
30074
null
-1
-1
3.1121
96

105
32000
69804
-1
null
5
null

102
30074
null
-1
96
77.1814
96

ID	CLASSID	NAME	VALUE
100	30074	TYPE-SPEC	SJ;3;1
100	30074	HREF	-1
100	30074	HMETHOD	96
100	30074	GEO_METHOD	96
100	30074	HPRCSN	2.7676
101	30074	TYPE-SPEC	SJ;3;1
101	30074	HREF	-1
101	30074	HMETHOD	96
101	30074	HPRCSN	3.04
102	30074	TYPE-SPEC	SJ;3;1
102	30074	HREF	-1
102	30074	HMETHOD	96
102	30074	GEO_METHOD	96
102	30074	HPRCSN	77.1814
103	30074	TYPE-SPEC	SJ;3;1
103	30074	HREF	-1
103	30074	HMETHOD	96
103	30074	GEO_METHOD	-1
103	30074	HPRCSN	3.1121
105	32000	ID	69804
105	32000	HREF	-1
105	32000	HPRCSN	5
106	32000	ID	73576
106	32000	HREF	-1
106	32000	HPRCSN	5
107	32000	ID	73589
107	32000	HREF	-1
107	32000	HPRCSN	5
108	32000	ID	74015
108	32000	HREF	-1
108	32000	HPRCSN	5

ID	CLASSID	IDVALUE	HREF	GEOMETHOD	HPRCSN	HMETHOD
100	30074	null	-1	96	2.7676	96
107	32000	73589	-1	null	5	null
108	32000	74015	-1	null	5	null
101	30074	null	-1	null	3.04	96
106	32000	73576	-1	null	5	null
103	30074	null	-1	-1	3.1121	96
105	32000	69804	-1	null	5	null
102	30074	null	-1	96	77.1814	96