Verify that the row in the Oracle database column table does not exceed 2000 bytes

Question

Want to truncate the error string so that it exactly matches the column in the Oracle VARCHAR2 table (2000 BYTE)

Constructive forces:

• The main goal is to fit the table column.

• 90-95% of the string text is an exception and stacktraces message. But it may contain some kind of client name with French, Turkish characters, which I am ready to ignore and see how? or something else.

• I want the code to be dead simple. database coding may change. You can enter hieroglyphs, but I want the code to work anyway.

It should be "dead simple", but it made me think for a while.

What are the suggestions?

Probably the best options are to convert to ascii. But I came up with an option that is not very pleasant, but probably works.

public static String trimStringToBytes(StringBuilder builder, int maximumBytes)
{
    String truncatedString = builder.length() > maximumBytes ?  builder.substring(0, maximumBytes) : builder.toString();

    byte[] bytes;
    String asciiCharsetName = "US-ASCII";
    try
    {
        bytes = truncatedString.getBytes(asciiCharsetName);
    }
    catch (UnsupportedEncodingException e)
    {
        //not really possible as JVM should support always US-ASCII but anyway
        int worstCaseScenarioBytesPerCharacter = 4;
        bytes = truncatedString.substring(0, truncatedString.length() / worstCaseScenarioBytesPerCharacter).getBytes();
    }

    return new String(bytes, 0, bytes.length > maximumBytes ? maximumBytes : bytes.length);
}

Answer 1

I would recommend not to do this in Java, but instead in SQL when you execute INSERT.

For example, in Oracle, you can use the SUBSTR function to trim using connection.prepareStatement:

insert into mytable (col1, col2) values (?, substr(?, 0, 2000));

Then, set values col1and col2in PreparedStatement, and Oracle should receive the first 2000 characters / bytes / whatever something-oo-oo-value, and fix it.

, , VARCHAR2 , . , .

Answer 2

, CLOB.

Answer 3

, , , ASCII, . ,

SQL . , . , Java.

Answer 4

Is it possible to change the column to VARCHAR2 (2000 CHAR)? This will completely eliminate the encoding problem.