What is correct way of inserting/updating clob in utf8

[tonyt]

Hello,

The below code successfully executes on one of 2 Oracle databases. When pointing to the second database (which is of exactly same structure as the first, runs on the same server as the first but the only difference is that the second is of character type (nls_characterset) is set to "UTF8" and the first to "US7ASCII."

The problem is when trying to write a data more than about 30000 bytes into the clob field I get "ORA-03120: two-task conversion routine: integer overflow". If I change the Orasession.Options.Charset to UTF8, i can write double the data but still gets the same error when going more than that.

I tried a few of the options I could find on this forum with regards to unicode etc but al fails.

My question, what is the correct way of inserting/update to a clob field when the database is set to UTF8 as explained above. Below is what I have tried so far, but all fails with the same message.

OraSession1.Options.Charset := 'UTF8';
with qSA do
begin
ParamCheck := true;
sql.clear;
SQL.Add('INSERT INTO POS_EMAIL');
SQL.Add('(EMAIL_MESSAGE_NO, SEND_DATE, FROM_EMAIL_USERNAME, SUBJECT_DESC, message_desc, TRANSMISSION_STATUS_IND, DELETE_IND, MESSAGE_SIZE_NO, LAST_UPDATE_DATE, USERNAME) ');
SQL.Add('VALUES ');
SQL.Add(' (:EMAIL_MESSAGE_NO,:SEND_DATE, :FROM_EMAIL_USERNAME, :SUBJECT_DESC, EMPTY_CLOB(), :TRANSMISSION_STATUS_IND, :DELETE_IND, :MESSAGE_SIZE_NO, :LAST_UPDATE_DATE, :USERNAME) ');
SQL.Add('RETURNING ');
SQL.Add(' MESSAGE_DESC ');
SQL.Add('INTO ');
SQL.Add(' :MESSAGE_DESC');

if not(Prepared) then
Prepare;
end;

and then for the clob section:

ParamByName('MESSAGE_DESC').ParamType := ptInput; ////
ParamByName('MESSAGE_DESC').DataType := ftOraClob;
ParamByName('MESSAGE_DESC').AsOraClob.IsUnicode := True;
ParamByName('MESSAGE_DESC').AsOraClob.LoadFromFile('c:\test.html');

I also tried AsOraClob.AsWideString := LoadMessage where loadmessage reads into stringlist and result returned as widestring.

I am using Odac Ver7.00.0.1 for Delphi.

Thank you.

[AlexP]

hello,

We cannot reproduce the described problem. Please try to reproduce the problem on the latest ODAC 8.5.9, if the problem repeats, please specify the exact versions of the Oracle Server, Oracle Client (if the OCI mode is used), Delphi, and and the result of the queries:

Code: Select all

SELECT USERENV ('language') from dual;

SELECT * FROM V$NLS_PARAMETERS;

[tonyt]

Thank you, it works fine with the latest version. I will have to upgrade then.

[AlexP]

hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.