Thanks for the question, Michael.

Asked: July 24, 2017 - 10:39 am UTC

Last updated: December 13, 2017 - 2:05 am UTC

Version: 12.2c

Viewed 10K+ times! This question is

You Asked

Hi, Tom.
I'm trying to extract a very long string into clob from json_object_t and got some weird database behaviour (12.2c) with json_object_t.get_clob(key) method. Here is a sample code:

DECLARE
  l_data CLOB := '{"text": "very long string about 1M chars"}';
  l_json json_object_t;
  l_text CLOB := EMPTY_CLOB();
BEGIN
  l_json := json_object_t.parse(l_data);
  l_text := l_json.get_clob('text');
  dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');
END;

When string length in a 'text' key is less than 32k chars, get_clob method works just fine and shows appropriate result, but with longer strings it produces an empty clob with zero length, just like get_string, but without 'character string buffer too small' exception.
I've tried to get same data via json_table query, but it cannot extract data to clob column at all, only varchar/number is allowed.
Is that a bug or am I doing something wrong? Is there any other ways to extract long strings from JSON keys?

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/file/content_FBKFMS018BUV9D7I64FUVGOKB.html

and Chris said...

How exactly are you creating your JSON documents? PL/SQL clobs are really varchar2(32767) unless you create it explicitly or get one from a table:

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

Do this and I can extract 1M+ character strings:

DECLARE 
  l_data CLOB; 
  l_json json_object_t; 
  l_text CLOB := EMPTY_CLOB(); 
BEGIN 
  dbms_lob.createtemporary(l_data,true);
  l_data := '{"text": "';
  for i in 1 .. 50 loop
    l_data := l_data || lpad('x', 32767, 'x'); 
  end loop;
  l_data := l_data || '"}'; 
  l_json := json_object_t.parse(l_data); 
  dbms_lob.freetemporary(l_data);
  l_text := l_json.get_clob('text'); 
  dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars'); 
END;
/

got 1638350 chars

Rating

(5 ratings)

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

Comments

Alex, October 30, 2017 - 5:39 pm UTC

I was wondering if CLOB is in fact the recommended type to store JSON data, assuming it's over 4k characters as opposed to a BLOB?

Thank you.

October 30, 2017 - 5:52 pm UTC

From the docs:

Oracle recommends that you use BLOB, not CLOB storage.

https://docs.oracle.com/database/122/ADJSN/overview-of-storage-and-management-of-JSON-data.htm#ADJSN-GUID-26AB85D2-3277-451B-BFAA-9DD45355FCC7

This avoids character set conversions and can save space.

Alex, October 30, 2017 - 6:14 pm UTC

I saw that, but I didn't think it was that simple (never usually is with Oracle).

I read other opinions talking about the ease of manipulating document text vs binary data, and using SQL in general against a BLOB vs CLOB.

I don't think I came across one example on this site of a JSON being stored in a BLOB despite this recommendation.

October 31, 2017 - 1:22 pm UTC

Well, if you're using blobs you may need to convert the strings to raw/blob when inserting them. Which is a bit of a faff for a quick demo.

You can find further discussion about this from the JSON team at:

https://blogs.oracle.com/jsondb/storing-json-in-blob-columns

from 12.2 got Zero chars

Rajeshwaran Jeyabal, October 31, 2017 - 1:48 pm UTC

Team:

Just ran the above piece of code in my local 12.2 instance and got zero chars in the output.

what version were you using to run this demo?

demo@ORA12C> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production                                   0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

demo@ORA12C> DECLARE
  2    l_data CLOB;
  3    l_json json_object_t;
  4    l_text CLOB := EMPTY_CLOB();
  5  BEGIN
  6    dbms_lob.createtemporary(l_data,true);
  7    l_data := '{"text": "';
  8    for i in 1 .. 50 loop
  9      l_data := l_data || lpad('x', 32767, 'x');
 10    end loop;
 11    l_data := l_data || '"}';
 12    l_json := json_object_t.parse(l_data);
 13    dbms_lob.freetemporary(l_data);
 14    l_text := l_json.get_clob('text');
 15    dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');
 16  END;
 17  /
got 0 chars

PL/SQL procedure successfully completed.

demo@ORA12C>

October 31, 2017 - 1:56 pm UTC

select * from v$version;

BANNER                                                                         CON_ID   
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production          0 
PL/SQL Release 12.2.0.1.0 - Production                                                0 
CORE 12.2.0.1.0 Production                                                            0 
TNS for Linux: Version 12.2.0.1.0 - Production                                        0 
NLSRTL Version 12.2.0.1.0 - Production 

DECLARE
  l_data CLOB;
  l_json json_object_t;
  l_text CLOB := EMPTY_CLOB();
BEGIN
  dbms_lob.createtemporary(l_data,true);
  l_data := '{"text": "';
  for i in 1 .. 50 loop
    l_data := l_data || lpad('x', 32767, 'x');
  end loop;
  l_data := l_data || '"}';
  l_json := json_object_t.parse(l_data);
  dbms_lob.freetemporary(l_data);
  l_text := l_json.get_clob('text');
  dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');
END;
/

got 1638350 chars

from 12.2 got Zero chars

Rajeshwaran Jeyabal, October 31, 2017 - 2:07 pm UTC

Did i miss something here?

I ran the same in 12.2, but different output why?

November 02, 2017 - 3:31 pm UTC

Hmmm, not sure...

What happens if you use a blob instead of a clob?

DECLARE
  l_data CLOB;
  l_json json_object_t;
  l_text BLOB := EMPTY_BLOB();
BEGIN
  dbms_lob.createtemporary(l_data,true);
  l_data := '{"text": "';
  for i in 1 .. 50 loop
    l_data := l_data || lpad('x', 32767, 'x');
  end loop;
  l_data := l_data || '"}';
  l_json := json_object_t.parse(l_data);
  dbms_lob.freetemporary(l_data);
  l_text := l_json.get_blob('text');
  dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');
END;
/

got 1638350 chars


PL/SQL procedure successfully completed.

from 12.2 got Zero chars

Rajeshwaran Jeyabal, November 03, 2017 - 12:38 pm UTC

Using BLOB works, but not on CLOB.

demo@ORA12C> DECLARE
  2    l_data CLOB;
  3    l_json json_object_t;
  4    l_text BLOB := EMPTY_BLOB();
  5  BEGIN
  6    dbms_lob.createtemporary(l_data,true);
  7    l_data := '{"text": "';
  8    for i in 1 .. 50 loop
  9      l_data := l_data || lpad('x', 32767, 'x');
 10    end loop;
 11    l_data := l_data || '"}';
 12    l_json := json_object_t.parse(l_data);
 13    dbms_lob.freetemporary(l_data);
 14    l_text := l_json.get_blob('text');
 15    dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');
 16  END;
 17  /
got 1638350 chars

PL/SQL procedure successfully completed.

demo@ORA12C> DECLARE
  2    l_data CLOB;
  3    l_json json_object_t;
  4    l_text clob := EMPTY_clob();
  5  BEGIN
  6    dbms_lob.createtemporary(l_data,true);
  7    l_data := '{"text": "';
  8    for i in 1 .. 50 loop
  9      l_data := l_data || lpad('x', 32767, 'x');
 10    end loop;
 11    l_data := l_data || '"}';
 12    l_json := json_object_t.parse(l_data);
 13    dbms_lob.freetemporary(l_data);
 14    l_text := l_json.get_clob('text');
 15    dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');
 16  END;
 17  /
got 0 chars

PL/SQL procedure successfully completed.

demo@ORA12C>

December 13, 2017 - 2:05 am UTC

Looks like a characterset issue.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

from 12.2 got Zero chars

from 12.2 got Zero chars

from 12.2 got Zero chars

More to Explore

PL/SQL demos

PL/SQL docs

Extracting very long string from JSON to CLOB

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

from 12.2 got Zero chars

from 12.2 got Zero chars

from 12.2 got Zero chars

More to Explore

PL/SQL demos

PL/SQL docs