Very Helpful
Rob Beers, September 19, 2017 - 2:19 pm UTC
This was very helpful. We will continue to monitor the bug.
To Understand JSON better
Jimmy, September 25, 2017 - 10:03 am UTC
September 27, 2017 - 10:31 am UTC
Thanks for sharing
What is the number of this bug.
anynymous, November 16, 2017 - 2:10 pm UTC
Hi
Found this most usefull.
We are currently starting project which would use JSON messages.
This size restriction is a little bit of a nuisance.
I tried to find out from mos what is the bug for this and couldn't find it.
Could You inform the bug number so we could monitor it and apply it as soon as possible.
Thanks.
November 16, 2017 - 4:54 pm UTC
The bug isn't published unfortunately, so you can't see it...
Pretty print the JSON
Rajeshwaran Jeyabal, November 18, 2017 - 1:19 am UTC
In addition to the above tool, we can also Pretty print the JSON in the database for better visualization.
demo@ORA12C> select json_object('empid' value empno,
2 'ename' value ename,
3 'hiredate' value hiredate,
4 'comm' value comm,
5 'sal' value sal
6 format json absent on null ) x1
7 from emp
8 where deptno = 10 ;
X1
-------------------------------------------------------------------------------------
{"empid":7782,"ename":"CLARK","hiredate":"1981-06-09T00:00:00","sal":2450}
{"empid":7839,"ename":"KING","hiredate":"1981-11-17T00:00:00","sal":5000}
{"empid":7934,"ename":"MILLER","hiredate":"1982-01-23T00:00:00","sal":1300}
demo@ORA12C> select json_query( json_object('empid' value empno,
2 'ename' value ename,
3 'hiredate' value hiredate,
4 'comm' value comm,
5 'sal' value sal format json absent on null ),'$' pretty) x1
6 from emp
7 where deptno = 10 ;
X1
-------------------------------------------------------------------------------------
{
"empid" : 7782,
"ename" : "CLARK",
"hiredate" : "1981-06-09T00:00:00",
"sal" : 2450
}
{
"empid" : 7839,
"ename" : "KING",
"hiredate" : "1981-11-17T00:00:00",
"sal" : 5000
}
{
"empid" : 7934,
"ename" : "MILLER",
"hiredate" : "1982-01-23T00:00:00",
"sal" : 1300
}
demo@ORA12C>
Patch
Steve, June 19, 2018 - 11:57 am UTC
I undeerstand there is now a patch available for the bug:
Patch 24693010: NLS:"JSON_ARRAYAGG/JSON_OBJECTAGG RETURNING CLOB" RETURNS GARBAGE CHARS"
June 19, 2018 - 1:09 pm UTC
18c also has full support for clobs in the JSON* functions :)
Bug json
A reader, July 20, 2018 - 6:51 am UTC
Is it possible to manage 200gb json file in oracle 12c?
If not what workarround in order to insert the content into oracle tables?
My requirements are to load such file and manipulate objects and values and generate quality reports of data.
July 20, 2018 - 9:11 am UTC
You have one 200Gb file?! Ouch!
What exactly are you trying to do with the contents? What errors are you hitting?
Big json
A reader, July 20, 2018 - 11:30 am UTC
Hi Chris
Before encountering a possible error it would be nice to find the way to load it.
What approach *you* take do it?
Since a clob a 4gb max accepted by oracle.
And if by miracle oracle permit 200gb clob it would be a perf issue??!
So how to do the parse and the load?
Does oracle or some ace tried a solution ?
July 23, 2018 - 10:39 am UTC
How are you trying to load it? What precisely are you doing? What happens when you do this?
Big json
Rajeshwaran, Jeyabal, July 22, 2018 - 3:41 pm UTC
....
Since a clob a 4gb max accepted by oracle.
And if by miracle oracle permit 200gb clob it would be a perf issue??!
....Team,
looking into the docs
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Data-Types.html#GUID-0EAC5929-0674-429C-AF42-2D454C982F8F it had this
....
If the tablespaces in your database are of standard block size, and if you have used the default value of the CHUNK parameter of LOB storage when creating a LOB column, then this is equivalent to (4 gigabytes - 1) * (database block size).
....so if we are on a Tablespace of standard block size 8K - then the max size of the CLOB storage would be close to 32TB rather than 4GB - is that right?
demo@ORA12C> select ( ((4*1024*1024*1024)-1) *8*1024 ) /1024/1024/1024 as size_gb from dual;
SIZE_GB
----------
32768
July 23, 2018 - 10:42 am UTC
Yes
You ask my questiom
A reader, July 23, 2018 - 11:15 am UTC
"How are you trying to load it? What precisely are you doing? What happens when you do this? "
Well this is what I am looking for.
How you load a 200gb json?
Can one put it in a clob?
If yes . What tbs configuration should have and what about performance using json for on it .(12.1.0.2)
If not. What workarround.?
Amazing service
A reader, July 23, 2018 - 1:13 pm UTC
I really thank you a lot Chris.
Very speed reply.
Do you want to load it as-is? Or shred the contents to traditional relational tables (my preferred method)?
How are you getting the file?
- sent from another appli. And put on java server.
Where precisely does it exist before you load it into the table?
- my requirements is performance
Is this a one-off load? Or will you need to load many files this size?
- many file per day from dozen mb to hundreds GB
Yes, you can load it into a clob if you want. But we recommend storing (large) JSON in a blob
- the goal is to parse a do checks on values for business . How tell the ext appli r provide a blob?
I prefer also your preferred solution.
Do you have an example to do the job?
Example
A reader, July 23, 2018 - 4:38 pm UTC
Could you pls provide simple example of how load a json into a blob col.
And then dispatch content few tables in db?
Maybe is 12.1.0.2
Ps. What I meant above is in fact the external application provide a text file json. My question was how to convert it to blob and then operate json function on it. Or how could the external application provide instead a blob . But since 12.1 is buggy and limit 4000b for return function I afraid that will not fit as my json is hundred of GB?!
July 25, 2018 - 1:29 pm UTC
The link in my previous comment has code showing you how to load clob data using Java. Blob is similar. Read the article.
You don't need to convert the text file to blob first. Just load its contents into a blob.
The comment below has an example of how to load a text file on the database server into a blob using PL/SQL. And manipulating it using json* functions.
Example
Rajeshwaran, Jeyabal, July 24, 2018 - 3:12 pm UTC
Could you pls provide simple example of how load a json into a blob col.
And then dispatch content few tables in db? lets say i have a json in a text file, then you could load that into a BLOB datatype column like this.
demo@ORA12C> create table t (x blob constraint t_chk check(x is json) );
Table created.
demo@ORA12C> declare
2 l_bfile bfile := bfilename ('TMP','sample_json.txt');
3 l_data blob ;
4 begin
5 insert into t(x) values( empty_blob() )
6 returning x into l_data;
7 dbms_lob.open( l_bfile );
8 dbms_lob.loadfromfile( l_data, l_bfile, dbms_lob.getlength(l_bfile) );
9 dbms_lob.close( l_bfile );
10 commit;
11 end;
12 /
PL/SQL procedure successfully completed.
demo@ORA12C> select dbms_lob.getlength(x) from t;
DBMS_LOB.GETLENGTH(X)
---------------------
420
demo@ORA12C> $type d:\trash\sample_json.txt
{
"items": [
{
"id": "111A",
"someAttributes": [
{
"name": "anAttribute",
"value": "A Value"
},
{
"name": "aDifferentAttribute",
"value": "A Different Value"
}
],
"someOtherAttributes": [
{
"name": "anotherAttribute",
"value": "Another Value"
},
{
"name": "lookAnAttribute",
"value": "Look, another value!"
}
]
}
]}
then you could turn that into a relational format using JSON function(s) and have than loaded into required tables like this.
demo@ORA12C> select item_id,
2 case when exists1 =1 then fname
3 when exists2 =1 then fname1 end as fname,
4 case when exists1 = 1 then fvalue
5 when exists2 = 1 then fvalue1 end as fvalue
6 from (
7 select *
8 from t, json_table( x, '$.items[*]'
9 columns(
10 item_id varchar2(10) path '$.id',
11 nested path '$.someAttributes[*]' columns(
12 fname varchar2(30) path '$.name',
13 fvalue varchar2(30) path '$.value',
14 exists1 number exists path '$.name') ,
15 nested path '$.someOtherAttributes[*]' columns(
16 fname1 varchar2(30) path '$.name',
17 fvalue1 varchar2(30) path '$.value',
18 exists2 number exists path '$.name')
19 ) )
20 )
21 /
ITEM_ID FNAME FVALUE
---------- ------------------------------ ------------------------------
111A anAttribute A Value
111A aDifferentAttribute A Different Value
111A anotherAttribute Another Value
111A lookAnAttribute Look, another value!
demo@ORA12C>