CODEWITHSUNDEEP
sqloracle-databaseexception
Batman
Batman

Reputation: 1324

VARCHAR2(1000) limited to 100 chars only

When trying to store in an Oracle SQL table a string of more than 100 chars, while the field limitation is 1000 bytes which I understood is ~1000 English chars, I'm getting out of bounds exception:

StringIndexOutOfBoundsException: String index out of range: -3

What might be the cause for this low limitation?

Thanks!

EDIT :

The code where the error occurs is (see chat):

// Commenting the existing code, because for sensitive information
// toString returns masked data
int nullSize = 5;
int i = 0;

// removing '[' and ']', signs and fields with 'null' value and also add
// ';' as delimiter.
while (i != -1) {
    int index1 = str.indexOf('[', i);
    int index2 = str.indexOf(']', i + 1);
    i = index2;
    if (index2 != -1 && index1 != -1) {
        int index3 = str.indexOf('=', index1);
        if (index3 + nullSize > str.length() || !str.substring(index3 + 1, index3 + nullSize).equals("null")) {
            String str1 = str.substring(index1 + 1, index2);
            concatStrings = concatStrings.append(str1);
            concatStrings = concatStrings.append(";");
        }
    }
}

Upvotes: 0

Views: 1328

Answers (2)

C.Champagne
C.Champagne

Reputation: 5489

Generally, when the string to store in a varchar field is too long, it is cropped silently. Anyway when there is an error message, it is generally specific. The error seems to be related to a operation on a string (String.substring()?).

Furthermore, even when the string is encoded in UTF-8, the ratio characters/bytes shouldn't be that low.

You really should put the code sample where your error occurs in you question and the string causing this and also have a closer look at the stacktrace to see where the error occurs.

From the code you posted in your chat, I can see this line of code :

String str1 = str.substring(index1 + 1, index2);

You check that index1 and index2 are different than -1 but you don't check if (index1 + 1) >= index2 which makes your code crash.

Try this with str = "*]ab=null[" (which length is under 100 characters) but you can also get the error with a longer string such as "osh]] [ = null ]Clipers: RRR was removed by user and customer called in to have it since it was an RRT".

Once again the size of the string doesn't matter, only the content!!!

You can reproduce your problem is a closing square bracket (]) before an opening one([) and between them an equal sign (=) followed (directly or not) by the "null" string.

Upvotes: 2

sulica
sulica

Reputation: 111

I agree with Jonathon Ogden "limitations of 1000 bytes does not necessarily mean 1000 characters as it depends on character encoding".

I recommend you to Alter column in your Oracle table from VARCHAR2(1000 Byte) to VARCHAR2(1000 Char).

Upvotes: 0

Related Questions