X

A blog about JSON in the Oracle database Technology

  • June 2, 2015

Storing JSON in BLOB columns

Beda Hammerschmidt
Consulting (Coding) Member of Technical Staff

In previous postings we have shown examples of JSON data
stored in a VARCHAR2 and CLOB columns. VARCHAR2
values have a maximum length of is 32767 bytes whereas CLOB has no such limitation.
Both are textual data types so any
selected value (if displayed in SQLPlus or SQLDeveloper) is directly readable. But there are reasons why you may want to
consider storing your JSON data as BLOBs:

CLOB data is stored in UCS2 encoding which uses two bytes
per character (similar to UTF16). For
JSON in most common UTF8 encoding (one byte per ASCII character) this means that
the storage size may increase (even double if most characters are ASCII). Also
IO would increase during queries and inserts.

On the other hand, storing your JSON data in a BLOB column
(Binary Large Object) does not have these disadvantages: here the UTF8 encoded
representation of JSON can be written and read without modifications. (This
also helps if the database character set is not AL32UTF8, since no character
set conversion is performed.)

So how does our example look for BLOBs? The table creation
is only slightly different.

CREATE TABLE colorTab
(
id NUMBER,
color BLOB);

The check constraint (making sure only valid JSON data gets
inserted) is the same:

ALTER TABLE colorTab


ADD CONSTRAINT ensure_json CHECK (color IS JSON STRICT);

To
insert textual data into the binary data type BLOB we convert the text data
type to a binary data type.

INSERT INTO colorTab
VALUES(1, utl_raw.cast_to_raw ('

{
"color": "black",
"rgb": [0,0,0],
"hex": "#000000"

}
'));

The
function ‘utl_raw.cast_to_raw’ performs the type casting so that the insert
succeeds if you issue the insert operation inside SQL (e.g. in SQL-Plus or SQLDeveloper). Important: you can directly insert bytes into the BLOB column from a
client like JDBC, no conversion is needed!

What
happens if you select the BLOB value? Since it is a binary data type it will be
displayed as hex by default (something like 0A7B0A20202022636F6C6F72223A2022…).
This is obviously not readable so we need to perform the inverse operation on a
select if we want to see the data as text:

select
utl_raw.cast_to_varchar2(color) from colorTab;

------------------------------------------------------------------------
{
"color": "black",
"rgb": [0,0,0],
"hex": "#000000"

}

Again,
if you read the data from a client like JDBC you can read the BLOB bytes
directly.

Note: the functions ‘utl_raw.cast_to_raw’
accepts a VARCHAR2 as input which is as we know limited to 32767 bytes.

How do we insert larger data? By converting a CLOB to a BLOB:


create or replace function clobToBlob( c IN CLOB ) RETURN BLOB is
  b     BLOB;
  warn  VARCHAR2(255);
  cs_id NUMBER := NLS_CHARSET_ID('AL32UTF8');
  do    NUMBER := 1; -- dest offset 
  so    NUMBER := 1; -- src offset 
  lc    NUMBER := 0; -- lang context
BEGIN
   DBMS_LOB.createTemporary(b, TRUE );
   DBMS_LOB.CONVERTTOBLOB(b, c, DBMS_LOB.LOBMAXSIZE, do, so, cs_id, lc, warn);
   RETURN b; 
END clobToBlob;
/



This
insert now looks like this

INSERT
INTO colorTab VALUES(1, clobToBlob ('

{
"color": "black",
"rgb": [0,0,0],
"hex": "#000000"

}'));


Join the discussion

Comments ( 1 )
  • Sergiusz Saturday, November 4, 2017
    If the internal BLOB storage character encoding is UTF-8, then UTL_RAW.CAST_TO_RAW and UTL_RAW.CAST_TO_VARCHAR2 are valid only in an AL32UTF8 database. UTL_I18N.STRING_TO_RAW and UTL_I18N.RAW_TO_CHAR should be used instead.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.