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 aClob
, set the data on it, and set it on the prepared statement. This will work for smaller datause
preparedStatement.setClob(position, reader)
- here you will have aReader
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
Java Fast Food Menu (Using Methods)
How to Connect Java Backend With Html/Css Frontend
Convert Localdatetime to Localdatetime in Utc
How to Clean Old Dependencies from Maven Repositories
How to Update Thousands of Records into MySQL Db in Milliseconds
Set Current Timezone to @Jsonformat Timezone Value
How to Make Sonar Ignore Some Classes for Codecoverage Metric
Mockito - Nullpointerexception When Stubbing Method
How to Check If an Excel Cell Is Empty Using Apache Poi
How to Call Methods Simultaneously in Java
How to Check Whether a Field Exists or Not in Mongodb
Http 415 Unsupported Media Type Error With Json
Checking If a Document Exists in a Firestore Collection
Java Jackson Deserialization of Nested Objects
Error: Incompatible Types: List<Integer> Cannot Be Converted to Arraylist<Integer>
Spring Data JPA - Consider Defining a Bean Named 'Entitymanagerfactory' in Your Configuration