[pavo123]

Hi Isomorphic,

I sent you an e-mail.

Best regards
Pavo

[Isomorphic]

Thank you, we are able to reproduce the problem with the information you sent. The issue is that your text contains extended-ASCII characters - German characters like Å - which are being converted into 2-byte UTF8 representation on their way into the database (this obviously assumes that your Oracle database is using the UTF8 character set, but we think that's a safe assumption as it is the default and that conversion is the reason for the error in our repro case). We are discussing internally the best way to deal with this, and will post back here when there is more to say.

[Isomorphic]

A fix for this problem has now been committed on 12.0 and 12.1 branches. Please try with tomorrow's builds (dated March 5th)

[pavo123]

Thank you very much!

[Blama]

Hi Isomorphic,

using v12.0p_2019-03-23 I can see the behavior with CLOBs changed, but I have trouble understanding way and it feels wrong to me.

We did indeed previously use parameterized statements for long text values in Oracle, but it had limitations when performing substring filtering. It was changed at the end of January / beginning of February to use precisely the technique the OP suggests - ie, carving long text values up into 4000-char chunks and concatenating. Testing insert of > 4000-char values into an Oracle database with the current codebase shows this to be working as intended, so this is probably just a case of needing to get the latest build.

As mentioned in post #5, there is a reason why we don't use parameter binding for this case. From your most recent post, we're not clear on what the issue is. Are you saying that 12.0p doesn't generate TO_CLOB() statements, or that it does generate TO_CLOB() statements but that doesn't solve the ORA-1704 problem? Or are you saying that it does generate TO_CLOB() statements and it does solve the problem, but you think it should solve the problem by using parameter binding instead?

I can see the mentioned behavior for INSERTs with

TO_CLOB('4000 chars')
     || '4000 chars',     || '4000 chars',     || '3000 chars')

but is this really correct (talking only about INSERTs here)? Also, I see ...' || '... after 1000 CHAR, but this is OK for me.

Although I can't find the thread right now I'm pretty sure the reason for the change you mention in the 1st linked post "It was changed at the end of January / beginning of February" was also by me. It was about WHERE conditions on those fields for SELECTs. Can you explain/link the thread?

For INSERTs the new non-parameter binding way generates VERY big SQLs and also the same amount of log data. To me, this just don't feels correct.

Thank you & Best regards
Blama

[claudiobosticco]

SmartClient Version: SNAPSHOT_v12.1d_2019-04-15/EVAL Deployment (expires 2019.06.14_07.21.11) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

Oracle 12c (12.2.0.1.0)

Hello, maybe the mentioned changes in this area have introduced a bug .

If I define a length on a text field (clob on the db) and this length is greater than 4000 (ie 4001), when I save a text (which actually is a short text, as a single word, say foobar), it saves on the db 'foobar' with single quotes around it.
Both for updates and inserts, If you save multiple times, it adds new quotes every time.

Note: it seems to have the same bug without the length and with type="clob", which however is not documented.

[Isomorphic]

This is fixed and is available for download in nightly builds since Apr 22 (today). Thank you for the report.

[claudiobosticco]

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

[claudiobosticco]

"SNAPSHOT_v12.1d_2019-06-03/EVAL Deployment"

Hello, it seems that there's a regression in the more recent builds, in the INSERT behaviour.

I've got a clob column, which is declared as type="text" in the dataSource, and in the insert I get the ORA-01704 error if the string to insert is more than 4000 characters long.

In the tomcat logs I see the full string in the values that is trying to insert, without TO_CLOB functions.

If I add an ample length to the field definition, it works and in the logs I don't see the string but only the '?' character.

[Isomorphic]

We haven't made additional changes in this area, so this is a bit mysterious.

Can you clarify what's happening here:

1. do you only receive an error if you are inserting exactly 4000 chars, with no error for lesser amounts?

2. do you receive an error even for small values (eg 100 char value)?

3. is the problem corrected if the field is just slightly more than 4000 (eg 4001)?

[claudiobosticco]

Hello, actually after a more careful look, I must say that the value which gives the error is actually less than 4000 chars, 3989 says BBEdit.

It's an html text, maybe is a problem with Oracle encoding, so that it becomes more than 4000 for Oracle?

I'll try to attach the text here html.txt

[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.

[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]

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

[claudiobosticco]

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