This may be a commonly asked question, but I did not get specific answer to my use case.
I received a requirement to store a JSON Object of size 40KB in the Oracle DB. I also need to encrypt it before storage using RSA .. So encrypted JSON will be larger than 40KB.
I did research and came to know that VARCHAR2 data type max size is 32K. So I cannot store my data in the VARCHAR2 data type column. The next option that Oracle offer is either BLOB or CLOB data type.
My question is.. which one I should choose? There are some posts that say to choose CLOB for Character data, and BLOB for the binary data. Another post on Oracle website says to use BLOB for JSON data (https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/overview-of-storage-and-management-of-JSON-data.html#GUID-26AB85D2-3277-451B-BFAA-9DD45355FCC7)
Since I have a JSON that is also encrypted, does it make sense to store that in CLOB, or should I use BLOB?
Encrypting JSON (with RSA or another algorithm) will convert it into a raw binary file. Once it's encrypted, Oracle (and anyone looking at it) can't tell that it's a JSON, so you should follow advice for storing raw binary data. You virtually always want to store raw binary files as BLOBs.
This does unfortunately mean that you can't use many of the features mentioned in Oracle's JSON Developer's Guide that you linked to, since Oracle can't tell that the column contains a JSON until you decrypt it.
As a side note, I do want to mention Oracle's TDE (transparent data encryption), which some other questions have covered. If you use Oracle's column encryption instead of manually encrypting, you could possibly still make use of Oracle's built-in JSON support (storing it as either CLOB or BLOB, adding an is_json check constraint to the column, using JSON functions, etc). Unfortunately, I don't believe you can use RSA public-key encryption with TDE, so it still might not work for your requirements.