Alternatives to Oracle CLOB for Storing JSON

Question

Are there any other alternative for storing large amounts of data other than CLOB in Oracle 10g? The maximum size of JSON file that I need to store in this field is 150Kb. Can I use VARCHAR2 or NTEXT for this purpose? The content of JSON file may be copied as text too if it is necessary to avoid using a CLOB. Thanks in advance for any help.

Answer 1

In 10g, the maximum size of a VARCHAR2 or NVARCHAR2 column is only 4kb. Under 12c, if you have the MAX_STRING_SIZE server property set to EXTENDED, this limit can be increased to 32kb, but still nowhere near 150kb.

You will either need to use CLOB, or break your 150kb down into 4kb chunks.

One option for breaking down your data is to store the data in a table where each row represents one line of the file:

file_id     line_id     line_data
---------------------------------
1           1           this
1           2           is
1           3           where
1           4           your
1           5           data
1           6           lives

Alternatively, if you can upgrade to 12c, you can take advantage of native JSON support.