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
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);
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 ('
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!
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:
utl_raw.cast_to_varchar2(color) from colorTab;
if you read the data from a client like JDBC you can read the BLOB bytes
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
cs_id NUMBER := NLS_CHARSET_ID('AL32UTF8');
do NUMBER := 1; -- dest offset
so NUMBER := 1; -- src offset
lc NUMBER := 0; -- lang context
DBMS_LOB.createTemporary(b, TRUE );
DBMS_LOB.CONVERTTOBLOB(b, c, DBMS_LOB.LOBMAXSIZE, do, so, cs_id, lc, warn);
insert now looks like this
INTO colorTab VALUES(1, clobToBlob ('