Thanks for the question, Raghavendra.
Asked: November 08, 2016 - 6:20 am UTC
Last updated: November 08, 2016 - 6:43 am UTC
Version: 11G
Viewed 1000+ times
You Asked
Hi Tom,
I have a data in a csv file like this:
something_here, "{
"entityValue" : {
"vlanId" : {
"type" : "string"
,
"value" : "121a"
},
"vlanDescription" : {
"type" : "string"
,
"value" : "asdf"
},
"ipAddress" : {
"type" : "string"
,
"value" : "1.0.0.1"
},
"ipMask" : {
"type" : "string"
,
"value" : "1.1.1.0"
},
"parentCode" : {
"type" : "string"
,
"value" : "asdf"
},
"nativeVlan" : {
"type" : "string"
,
"value" : "1"
}
}
}", "raghav"
I need to load this data into a table with following structure.
create table clob_data (
search_text varchar2(1000),
entityValue varchar2(1000),
vlanDescription varchar2(1000),
ipAddress varchar2(1000),
ipMask varchar2(1000),
parentCode varchar2(1000),
nativeVlan varchar2(1000),
name varchar2(1000)
)
with using the data as given above as:
something_here, 121a, asdf, 1.0.0.1, 1.1.1.0, asdf, 1, raghav
Could you please help me in identifying the way to do so?
Thanks alot.
Raghavendra
and Connor said...
There aren't any native JSON parsing facilities in 11g. Some options to consider:
1) your own processing using substr/instr etc, assuming the json structure consistent
2) look at the apex_json package in apex to parse the json
https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm 3) move to, or access the data from, release 12 which has better json facilties
Hope this helps