CODEWITHSUNDEEP
springoracle-database
Brarord
Brarord

Reputation: 651

How to storage huge String data in oracle database?

I have the following String variable: enter image description here As you can see this is JSON text but it is not converted to JSON yet i just holding this in String variable. I counted number of characters and it will be about 5000, maybe sometimes to 10000 characters max.

What is the best way to save that kind of data inside my Oracle Database and do not lose any character from that String? I mean i know conversion will be necessary so i just don't want to lose any character, you can see what kind of characters i am keeping in that string.

Can someone tell me is this possible to save that inside VARCHAR2 ? Or what should i do to properly save that data.

Upvotes: 0

Views: 4731

Answers (2)

Daniel Overby Hansen
Daniel Overby Hansen

Reputation: 36

You can increase the max size of a VARCHAR2 from Database 12.2: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/MAX_STRING_SIZE.html#GUID-D424D23B-0933-425F-BC69-9C0E6724693C

Otherwise, as mentioned before go with a CLOB.

Upvotes: 1

MT0
MT0

Reputation: 168361

what should i do to properly save that data.

Use a CLOB data type.

And from Oracle 12c you can add a CHECK constraint to ensure it is valid JSON:

CREATE TABLE table_name (
  value CLOB CHECK ( value IS JSON )
);

db<>fiddle

Can someone tell me is this possible to save that inside VARCHAR2?

As the Oracle documentation for the VARCHAR2 data type states:

The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column.

So if you have a 5,000 - 10,000 character string you cannot store it in a VARCHAR2 data type as it has a maximum size of up to 4,000 bytes.

If the JSON data has a fixed format then you could create tables for the nested objects in the JSON and store the key-value pairs from the JSON in columns of those tables; but if you want to store the complete JSON then you need to use a LOB data type and since JSON is character data then a CLOB is appropriate.

Upvotes: 3

Related Questions