When many of us jumped into the JSON soup and started playing with all of the JSON goodies in the Oracle Database, because JSON is just plain text, we naturally assumed that the logical place for storing our JSON data was in a CLOB column. The database understood JSON so we could ensure that our CLOB could only contain JSON with an appropriate CHECK constraint
SQL> create table t ( x int primary key, c clob , check( c is json));
Table created.
Then we could use the various JSON constructors to populate the column…
SQL> insert into t
2 select object_id, json_object(owner,object_name,object_type)
3 from dba_objects
4 where object_id is not null;
73660 rows created.
SQL> commit;
Commit complete.
and because the database knows that this is JSON we could also take advantage of things like the “dot notation” to access our data
SQL> select * from t
2 where rownum <= 10;
X C
---------- --------------------------------------------------------------------------------
132 {"owner":"SYS","object_name":"USER_EDITIONING$","object_type":"TABLE"}
133 {"owner":"SYS","object_name":"I_USER_EDITIONING","object_type":"INDEX"}
134 {"owner":"SYS","object_name":"ORA$BASE","object_type":"EDITION"}
135 {"owner":"SYS","object_name":"MIGRATE$","object_type":"TABLE"}
136 {"owner":"SYS","object_name":"VIEWCON$","object_type":"TABLE"}
138 {"owner":"SYS","object_name":"SYS_IL0000000136C00005$$","object_type":"INDEX"}
137 {"owner":"SYS","object_name":"SYS_LOB0000000136C00005$$","object_type":"LOB"}
139 {"owner":"SYS","object_name":"I_VIEWCON1","object_type":"INDEX"}
140 {"owner":"SYS","object_name":"I_VIEWCON2","object_type":"INDEX"}
141 {"owner":"SYS","object_name":"ICOLDEP$","object_type":"TABLE"}
10 rows selected.
SQL> select t.c.owner
2 from t t
3 where rownum <= 10;
OWNER
------------------------------
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
SYS
10 rows selected.
However, most of us later learned that because CLOBs must take care of things like characterset mapping between the client and the database, and the way LOBs are stored in multibyte characterset databases, that CLOB is more expensive from both a storage and CPU perspective than storing our JSON in binary (BLOB) format.
Unfortunately, once we discovered this piece of information, we could not simply ask the database to change our column to a BLOB.
SQL> alter table t modify c blob;
alter table t modify c blob
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype
Similarly, if we intended to add a BLOB column and then do a conversion with a simple UPDATE, this was also not possible.
SQL> alter table t add b blob;
Table altered.
SQL> update t set b = c;
update t set b = c
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BLOB got CLOB
We could overcome this by using a PL/SQL function to perform the conversion (and I’ll come back to this in a moment), but assuming the conversion was done, we still have a large mess to clean up. My table would now have both a CLOB and a BLOB column, and dropping that CLOB column would be a resource intensive operation in itelf. Also, during this exercise, the table rows will all get locked thus probably forcing a large application outage to complete the process.
Luckily, there is a better way. As I’ve covered in a previous post, if I am going to use BLOB format, I may as well take the opportunity to take advantage of the OSON format to get the best storage and performance out of my JSON. (If you are doing this on version 23ai, then the JSON datatype is all you need, but I’m assuming most of you are currently running 19c for your enterprise appications)
First I will create my target table
SQL> create table t1 (
2 x int, b blob,
3 check ( b is json format oson));
Table created.
Now I need a function to convert my CLOB to a BLOB. Unfortunately, DBMS_LOB does not have a native function to do it, but we can easily build a small wrapper around the CONVERTTOBLOB procedure,
SQL> create or replace
2 function clob2blob (p_clob clob) return blob deterministic as
3 l_tgt_idx int := 1;
4 l_src_idx int := 1;
5 l_blob blob;
6 l_lang int := dbms_lob.default_lang_ctx;
7 l_err int := dbms_lob.warn_inconvertible_char;
8 begin
9
10 dbms_lob.createtemporary(
11 lob_loc => l_blob,
12 cache => true);
13
14 dbms_lob.converttoblob(
15 dest_lob =>l_blob,
16 src_clob =>p_clob,
17 amount =>dbms_lob.lobmaxsize,
18 dest_offset =>l_tgt_idx,
19 src_offset =>l_src_idx,
20 blob_csid =>dbms_lob.default_csid,
21 lang_context=>l_lang,
22 warning =>l_err);
23
24 return l_blob;
25 end;
26 /
Function created.
With our function in place, we can utilise the column mapping options in DBMS_REDEFINITION to convert our CLOB to a our new cool OSON BLOB without almost no interruption to applications. Note – be careful to include a column alias for the function call in the COL_MAPPING clause otherwise you will get an error.
SQL> exec dbms_redefinition.can_redef_table(user, 't');
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_redefinition.start_redef_table(user, 't', 't1', col_mapping => 'x, clob2blob(c) b');
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_redefinition.finish_redef_table(user, 't', 't1');
PL/SQL procedure successfully completed.
Voila! Your JSON is now smaller and faster in a new home ready for your applications to exploit!
SQL> desc t
Name Null? Type
----------------------------- -------- --------------------
X NUMBER(38)
B BLOB
If you want to dive deeper into the JSON formats, check our this Office Hours session from the JSON team.
Learning is not a spectator sport 
Leave a reply to Rakesh Lenka Cancel reply