How to set DOA for UTF8 Support Client and Database (DOA cut always 4000 Chars to 4000bytes)?

[mschroeer]

We have a database with german_germany.al32utf8 and max_string_size=extended
and tables with varchar2(4000 char) columns.

So what must we do on doa, that we can save 4000 Chars in the database, doa always cut the data to 4000Bytes on saving.

If we have data already with 4000 Chars in the table, doa can load and show it, but on saving, the data always cut to 4000Bytes.

 

[Marco Kalter]

Which Delphi and DOA version are you using, and which component and code do you use to write the varchar2 value?

 

[mschroeer]

Delphi 13.3.3 and DOA 4.1.4.0

TOracleSession
Preferences.ConvertUTF = cuUTF8ToUTF16
Preferences.ForceWideStringFields = True

I use as TOracleDataSet with a TDBMemo.
If the table as 4000 Chars in the varchar2 field, it will show correctly.
But if i edit the memo, it will always save to 2000Bytes!

Example 2:

OracleQuery1.sql.text := 'update <table> set <field> = :bem where <primarykey> = <value>';
s := StringOfChar('Ü',4000);
OracleQuery1.DeclareAndSet('bem',otstring,s);
OracleQuery1.execute;
oraclesession1.Commit;

Result is always 2000Bytes (Dump(<field>,16) Typ=1 len=4000: c3,9c,c3,9c,...

 

[mschroeer]

Sorry, we use Delphi 10.3.3
Oracle DB 12.1.0.1.0

 

[mschroeer]

I made some test. The OracleMonitor show the variables before query. The variable length is already too short there (2000 characters).
Why is it being cut to the wrong length?
Why doesn't DOA recognize 4000 characters or the 16000 bytes that are possible in the DB?

 

[Marco Kalter]

The otString data type is limited to 4000 bytes. You need to use otPLSQLString instead:

OracleQuery1.DeclareAndSet('bem',otPLSQLString,s);

 

[mschroeer]

Ok. But there is no otPLSQLString in TOracleDataSet.

TOracleDataSet.sql.text := 'select <table>.*, rowid from <table>'
How do i it there? Is the same Problem as Toraclequery

 

[Marco Kalter]

String fields in a TOracleDataSet are indeed limited to 4000 bytes.

 

[mschroeer]

Why don't you support the entire Oracle database?
Why do you limit strings to 4000 bytes even though the Oracle database uses characters and UTF8? In Delphi, you can refer to chars rather than bytes.

What would be a solution instead of saying that TOracleDataSet strings can only be 4000 bytes?