One of our customer reports: Ora-01480 - Trailing null missing from STR bind value. (Ora10 database, delphi7, doa 4.0.6.2)

It happens on a update from a normal oracledataset. The size of the field in de database en tstringfield is 2000. The length of the string being inserterd is approximatly 1100 characters. What could be the problem?

Oracle monitor query generated:
update AROOSTERPLOEG
set ARP_DIENSTEN = :v1
where rowid = :doa__rowid
returning rowid into :doa__rowid

Can you let me know the character set of the database? The problem is probably related to that.

It seems the problem occurs at the sizes above 1000 characters. Th eprogram tries to store a string of 1001 characters.

During the import of their dump it say's:
import is uitgevoerd in WE8MSWIN1252 tekenset en AL16UTF16 NCHAR-tekenset.
importserver gebruikt WE8ISO8859P15 tekenset (mogelijke tekensetconversie).

I have send queries to that customer to get the info from their database: 'NLS_CHARACTERSET' and 'NLS_NCHAR_CHARACTERSET';

Hi Marco,

The database nls_database_parameters returns:
'NLS_CHARACTERSET' = AL32UTF8
'NLS_NCHAR_CHARACTERSET' = AL16UTF16

is this enough info for you?

thnx in advance
Ruud

Thanks. Have you set TOracleSession.BytesPerCharacter to bcAutoDetect? This is required for multi-byte character set databases.

Yes, the BytesPerCharacter property was allready set to bcAutoDetect.

I see now that you are using 4.0.6 on a Unicode database. For Unicode support you will need to upgrade to version 4.1.

Hello Marco,

thanks for your quick reply's

regards Ruud

Hello Marco,

We wrote a testprogram using the 4.1.0 trial but the problem remains the same.

Regards Ruud

Just to be sure: have you set TOracleSession.BytesPerCharacter to bcAutoDetect?

Hello, Marco,
Since some DOA users in JAPAN reported this same ORA-01480 error,
I'm currently investigating this with a small sample program.

With Server:
NLS_CHARACTERSET AL32UTF8
NLS_LENGTH_SEMANTICS BYTE
and Client:
NLS_LANG=JAPANESE_JAPAN.JA16SJISTILDE

I get ORA-01480 when I update a
VARCHAR2 (2020) column with a long (l>=1001) string.

BytesPerCharacter = 4,
MaxVarChar = 4000 / 4 = 1000.
----------
I've found that Session.MaxVarChar is so small that
TOracleQuery.BindVariables truncates the buffer too short.
----------
There seems to be a workaround.

function TOracleSession.MaxVarchar: Integer;
begin
if UseOCI80 then Result := 4000 else Result := 2000;
Result := Result div CharacterSize;
Result := 16000; //<< HERE is the worlkaround.
end;

By setting the MaxVarchar large enough,
DOA won't truncate the input varchar2 string any more.

But I'm not sure it is safe to do so yet.

Is this safe to set MaxVarchar = 160000 so that
it's large enough for VARCHAR2(4000 CHAR ) in AL32UTF8 ?

I hope the only bad thing with MaxVarchar=16000 is
larger memory consumption

Thanks, we'll check it out.

Yes the TOracleSession.BytesPerCharacter = bcAutoDetect.
I wil make a test program to check the workaround Kunikazu suggests. (thanks in advance for sharing this.)

Marco can you let me know if this is save to use?

I cannot yet say if it is safe. We have not yet tested this.

Hi Marco,

the workaround from Kunikazu does the trick. When can we expect an answer if it is ok to use in an productional environment.

thanks in advance

Ruud

I'm not sure yet. This will require some extensive testing.

Ok I understand,

i have to be more patient then "Szell" the dentist in "The Marathon man" ... "is it safe?" ;-)

kind regards

Has this issue been resolved yet?

We are facing the same issue - what is the status of this issue?
Can we expect a fix in the soon future?

I have the same problem. I use Oracle.NoUnicodeSupport:=True.
Using DOA 4.1.2.0, Delphi 2010.
Is it fixed now?

We do not yet have a fix for this, but we are working on it.

Hi Marco,
I see that patch 4.1.2.2 is available, we're still using 4.1.2.0.
Is this problem fixed? Thanks

Alberto Pilat

We may have a fix. I will upload a Delphi 2010 version shortly and will post a link here.

Very thanks, I'll wait for it
Alberto Pilat

Hi Marko,
some news about the problem? Thanks

We need to do some last tests, but I can upload a test version this week.

Recently was post a fix in 4.1.2.2. Is this the test version that you write about?
Alberto Pilat

Sorry Marco, but now the problem is getting urgent.
How long we have to wait yet?
Alberto

Not long. If you have the source code option I can already send you the source changes by e-mail.

Thanks, unfortunatly I haven't source code option, but it's enough to know the month when the fixed will be available. You can understand that we have to tell something to our customers...
I can wait but I pray you to tell me a date.
Bye
Alberto

Marco, what I have to do to enable the source code option? Can you post me the correction as you've said?

Is this resolved yet? Need a solution if possible. Will start looking for a work around in the meantime.

Version 4.1.3 will be available shortly.

It appears to me that 4.1.3 is still not available for XE (I did a new download from the registered page and the unpacked files are names *412.*. Is this issue resolved in the XE2 versions of the components? Is there any other way that I can get around this limitation?

Hi Marco,

we bought the last doa version and i expected this issue would be solved. We are using delphi7 and doa version 4.1.1 (Highest version available for delphi 7) We still get the trailing null error....

any suggestions?

Regards Ruud

Hi,

We have Delphi XE2 and DOA 4.1.3, and still getting this
"ora-01480" error. Is there a fix ready soon, or does anyone have another workaround? We do not have the source-code.

Regards,
Heimsater

Hi.
I wonder why a fix is still not availabale when this bug is known for over a year now. It is not that difficult to fix it. Look into TOracleQuery.BindVariables procedure located in in Oracle.pas We need this piece of code:
// Determine the maximum number of characters for a string or char variable
lBufSize := bufsize;
if (lBufType in [otString, otChar, otVarchar2]) and (BufType <> otPLSQLString) then
begin
if CharSetForm = ocfNational then
begin
if (lBufSize > Session.MaxVarcharN + 1) then lBufSize := Session.MaxVarcharN + 1;
end else
if (lBufSize > Session.MaxVarchar + 1) then lBufSize := Session.MaxVarchar + 1;
end;

It calculates buffer size of the string bind variable. Problem is, calculation is done in CHARACTERS when it needs to be in BYTES. Here is fixed version:
// Determine the maximum number of characters for a string or char variable
lBufSize := bufsize;
if (lBufType in [otString, otChar, otVarchar2]) and (BufType <> otPLSQLString) then
begin
//Buffer size must be calculated in bytes, not in characters!
if CharSetForm = ocfNational then
begin
if (lBufSize > Session.MaxVarcharN * Session.CurrentBytesPerNCharacter + 1) then
lBufSize := Session.MaxVarcharN * Session.CurrentBytesPerNCharacter + 1;
end else
if (lBufSize > Session.MaxVarchar * Session.CurrentBytesPerCharacter + 1) then
lBufSize := Session.MaxVarchar * Session.CurrentBytesPerCharacter + 1;
end;

Just copy+paste it instead of the old block, roll out a new version of DOA and everybody will be happy.

Hello,
what about this bug? I receive ora-01480 when I try with DXE3 + DOA 4.1.3.3 against a Oracle 9.2.
I use bind string variables, while if I use subst variables or build the entire statement it works.
Also, if I execute the same statement in PL/SQL using same string variables it works.
Thanks.

Sorry, but it was my mistake...I see now that I was using oci7 compatibility!
Without its, it works.

Hi Marco,

wie use CBuilder-2009 and we do not have the source-code.
So what can we do to solve the problem?
The post from snowmann looks like a working solution, why can't we get a fix for the bug?

>> Just copy+paste it instead of the old block, roll out a new version of DOA and everybody will be happy.

Thanks