How to Store a String Longer Than 4000 Characters in an Oracle Database Using Java/Jdbc

How do I store a string longer than 4000 characters in an Oracle Database using Java/JDBC?

You have (at least) two options:

  • use connection.createClob() to create a Clob, set the data on it, and set it on the prepared statement. This will work for smaller data

  • use preparedStatement.setClob(position, reader) - here you will have a Reader instance.

Oracle JDBC charset and 4000 char limit

Prior to Oracle 12.1, a VARCHAR2 column is limited to storing 4000 bytes of data in the database character set even if it is declared VARCHAR2(4000 CHAR). Since every character in your string requires 2 bytes of storage in the UTF-8 character set, you won't be able to store more than 2000 characters in the column. Of course, that number will change if some of your characters actually require just 1 byte of storage or if some of them require more than 2 bytes of storage. When the database character set is Windows-1252, every character in your string requires only a single byte of storage so you'll be able to store 4000 characters in the column.

Since you have longer strings, would it be possible to declare the column as a CLOB rather than as a VARCHAR2? That would (effectively) remove the length limitation (there is a limit on the size of a CLOB that depends on the Oracle version and the block size but it's at least in the multiple GB range).

If you happen to be using Oracle 12.1 or later, the max_string_size parameter allows you to increase the maximum size of a VARCHAR2 column from 4000 bytes to 32767 bytes.

oracle sql - to store 4000 characters in a column of my DB table

Did you check CLOB. For saving large Text oracle recommends CLOB.

A CLOB (Character Large Object) is an Oracle data type that can hold
up to 4 GB of data. CLOBs are handy for storing text.



Related Topics



Leave a reply



Submit