[claudiobosticco]

SmartClient Version: SNAPSHOT_v12.1d_2019-07-20/Enterprise Deployment (built 2019-07-20)

I can confirm it's fixed, thank you very much

[Isomorphic]

This is fixed and available for download in nightly builds since June 20 (today). Let us know please how it worked for you.

[claudiobosticco]

Hello, the version is:
DBMS: Oracle (ver. Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)

[Isomorphic]

Could you please share with us what exact Oracle version you are using? It seems that for you string splitting is entirely skipped and this most likely is caused by the Oracle version.

[claudiobosticco]

Sorry for the mess, I'll try to rephrase.

The (SmartClient generated) query doesn't work because it's trying to insert in the clob column a string which, in the Oracle charset, has more than 4000 characters. This query is in the attachment generated_query.sql.txt
generated_query.sql.txt

That String in java has .length() of 3989, while if I use .getBytes().length I get 4002.

I think that SmartClient is not splitting the string in the generated query because is using the former length measurement (instead it must use database charset?)

The field in the dataSource is declared as text without a length attribute. If I add a length="10000" attribute it works, but in the log in the generated query I don't see the full string, only the '?' (parameter binding is called, right?). Not sure if this is by design.

Without the length attribute Oracle raises an error even if the column is a Clob.

If I split the string in two, and use the TO_CLOB function for the two chunks, the query works (see attachment modified_query.sql.txt).
modified_query.sql.txt

[Isomorphic]

Just a moment ago you said you receive the same error when not even using JDBC, let alone SmartClient. So could you clarify why you now think that something in SmartClient is the problem?

[claudiobosticco]

Sorry, but the column is declared as a clob on the database table, so it's not the problem.

[Isomorphic]

Yes, that's apparently the problem. However, there's no way of putting the value into Oracle that would make it work. The problem is not whether the string is split up - it's just too large for your column.

[claudiobosticco]

We break it up into smaller strings automatically. But that's not going to help if the overall value still exceeds the column size, which is the problem here.

But isn't it the problem that the String in java is < 4000 and in Oracle charset is > 4000 ?

IE, if I assign my string in a java snippet to a foo variable, and then I execute:

System.out.println(foo.length());
and
System.out.println(foo.getBytes().length);

I get:

3989
and
4002

[Isomorphic]

We break it up into smaller strings automatically. But that's not going to help if the overall value still exceeds the column size, which is the problem here.

[claudiobosticco]

We have AL32UTF8 which is a multibyte character set, but now I wonder how could work your strategy of splitting in strings of 4000 characters for multibyte character sets...

[claudiobosticco]

yep, actually that 3989 characters text has length 4026 for Oracle...

[Isomorphic]

Yes, that suggests it's not SmartClient. Could be something like a smartquote in the data, or other invalidly encoded character.

[claudiobosticco]

Nope, that text is the exact text that I've copied from the query in the tomcat logs.

But now I see that I've got the same error even if I try to execute the attached query with two different oracle clients (one which uses the jdbc driver and another not using it), so it doesn't seem an issue related to SmartClient, right?

[Isomorphic]

By the time this is written into SQL, does it have encoding such as &lt; instead of "<"? That would put it over 4000.

If that's not the problem, please answer our other questions.