Thanks for the question, Tarkesh.

Asked: January 25, 2016 - 2:12 pm UTC

Last updated: January 27, 2016 - 5:11 pm UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

CLOB JSON extraction in to table
Hi Tom,

I am receiving a JSON object in to a source table in a column of CLOB datatype.

SourceTable
[id start_date json_data]

Now I have to extract all of that JSON field in to a table of other database ,

so that it can be fatched in microstrategy reports and avail;able to busioness users.

Each field of the clob data should be saved in different column of table to perform operations on that data.

Target Table
[id start_date json_data_field1 json_data_field2 .... ]

Issue #1 : The number of fields in CLOB are variable.
For example if in one record the CLOB field has 200 fields , for next records it may be 250 of 150 fields.

Issue#2 : Position of field are variable.
For example if address of student is in line# 5 in one clob , it might be at line# 250 for next clob.

The nature of populating JSON data into that CLOB field is dynamic.

Could you please help how to extract this dynamic clob data into other table.

and Chris said...

If you're on 12c (12.1.0.2), you can define an "is json" check constraint on the table. This enables you to access the values using dot-notation or functions such as json_table, json_query, etc.:

https://oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1

You say you're on 10g. So you'll need to use string manipulation. If you want help with this you'll need to give us some sample data along with the output you expect from them.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot Chris

Tarkesh, January 25, 2016 - 4:12 pm UTC

Thanks a lot Chris for your prompt response.

My JSON field format:

{
"form" : {
"links" : [ {
"rel" : "self",
"uri" : " http://abc.co.uk:80/rest/application/"
}, {
"rel" : "submit",
"uri" : " http://abc.co.uk:80/rest/application/submit"
}, {
"rel" : "create_form",
"uri" : " http://abc.co.uk:80/rest/application/ft1516/Form"
} ],
"crn" : "41366293",
"id" : "4376610",
"Year" : 2015,
"startYear" : 2013,
"sourceType" : "ONLINE",
"active" : true,
"Date" : 1452092084098,
"contact1" : {
"surname" : "Hader",
"forename" : "Smon",
"county" : "Hertfordshire",
"postcode" : "AL12 1TX",
"address1" : "13 De Tany Court",
"address2" : null,
"address3" : null,
"town" : "ST. ALBS",
"country" : "UK",
"links" : [ ]
},
"contact2" : {
"surname" : "Halder",
"forename" : "John",
"county" : null,
"postcode" : "SL6 8HW",
"address1" : "18 Meadfield Road",
"address2" : null,
"address3" : null,
"town" : "SLOH",
"country" : "UK",
"links" : [ ]
},
"domicile" : null

}
}

additionally the position of these field can be shuffle and number of fields can be increased or decreased in next json record.e.g only contact1 can be there or upto contact5 can be arrived in next CLOB record.

I have issue with the dynamically changing CLOB fields to put them in any table.

Target table would be :

id start_date form rel crn id ... contact1 contact2 domicile

Could you please suggest some ways to extract and handle the dynamic number of fields from this CLOB column.

January 26, 2016 - 1:07 am UTC

Take a look here

http://sourceforge.net/p/pljson/wiki/Home/

You might find a lot of work has been done for you.

Cheers,
Connor

Thanks Corner

Tarkesh, January 26, 2016 - 10:50 am UTC

Thanks for your response.

http://sourceforge.net/p/pljson/wiki/Home/

is helpful for static JSON CLOB .

We have repetitive columns names like 'forename' is at more than 6 different places somewhere for user , parents , references etc.
so searching the right value at right field is an issue.
Moreover we have variable number of fields in JSON , so structuring those fields in to the target table is a challenge.

e.g some JSON record will have 150 field and other can have 500 fields .

Could you please suggest , if it is doable in Oracle 10g.

January 26, 2016 - 2:14 pm UTC

You can use dot notation with PL/JSON to access elements e.g. "user.forename", "parents.forename", etc.:

https://github.com/pljson/pljson/blob/master/examples/ex8.sql

It also has functions for you to check whether elements exist. So if this doesn't support your use case then I think you may be out of luck...

Chris

Getting error while compiling the code

Tarkesh, January 26, 2016 - 3:54 pm UTC

Thanks Chris for your response.

I am getting error while executing below code in Oracle 10g.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:9525777900346465790

Error description:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

obj json := json(
*
ERROR at line 2:
ORA-06550: line 2, column 7:
PLS-00201: identifier 'JSON' must be declared
ORA-06550: line 2, column 7:
PL/SQL: Item ignored
ORA-06550: line 22, column 3:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 22, column 3:
PL/SQL: Statement ignored
ORA-06550: line 29, column 10:
PLS-00201: identifier 'JSON_LIST' must be declared
ORA-06550: line 29, column 10:
PL/SQL: Item ignored
ORA-06550: line 30, column 14:
PLS-00201: identifier 'JSON_VALUE' must be declared
ORA-06550: line 30, column 14:
PL/SQL: Item ignored
ORA-06550: line 31, column 13:
PLS-00201: identifier 'JSON' must be declared
ORA-06550: line 31, column 13:
PL/SQL: Item ignored
ORA-06550: line 33, column 8:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 33, column 5:
PL/SQL: Statement ignored
ORA-06550: line 45, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 45, column 7:
PL/SQL: Stat

Please suggest.

January 27, 2016 - 5:11 pm UTC

Have you downloaded and installed the PL/JSON code we linked to?

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer