ora-01480

Ruudbern · Mar 2, 2010

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

Marco Kalter · Mar 2, 2010

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

Ruudbern · Mar 2, 2010

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

Ruudbern · Mar 2, 2010

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

Ruudbern · Mar 3, 2010

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

Marco Kalter · Mar 4, 2010

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

Ruudbern · Mar 4, 2010

Yes, the BytesPerCharacter property was allready set to bcAutoDetect.

Marco Kalter · Mar 5, 2010

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.

Ruudbern · Mar 9, 2010

Hello Marco,

thanks for your quick reply's

regards Ruud

Ruudbern · Mar 18, 2010

Hello Marco,

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

Regards Ruud

Marco Kalter · Mar 19, 2010

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

Kunikazu Okada · Mar 23, 2010

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; //

Marco Kalter · Mar 24, 2010

Thanks, we'll check it out.

Ruudbern · Mar 26, 2010

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?

Marco Kalter · Mar 29, 2010

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

Ruudbern · Apr 1, 2010

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

Marco Kalter · Apr 1, 2010

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

Ruudbern · Apr 1, 2010

Ok I understand,

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

kind regards

F.Hinlopen · Nov 4, 2010

Has this issue been resolved yet?

Arvid · Dec 8, 2010

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

Alberto Pilat · Jan 26, 2011

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

Marco Kalter · Jan 27, 2011

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

Alberto Pilat · Mar 3, 2011

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

Marco Kalter · Mar 4, 2011

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

Alberto Pilat · Mar 4, 2011

Very thanks, I'll wait for it
Alberto Pilat

Alberto Pilat · Mar 18, 2011

Hi Marko,
some news about the problem? Thanks

Marco Kalter · Mar 21, 2011

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

Alberto Pilat · Apr 21, 2011

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

Alberto Pilat · Jun 23, 2011

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

Marco Kalter · Jun 24, 2011

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

Alberto Pilat · Jun 29, 2011

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

Alberto Pilat · Jul 13, 2011

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

Cory Jay · Dec 8, 2011

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

Marco Kalter · Dec 9, 2011

Version 4.1.3 will be available shortly.

Cory Jay · Mar 14, 2012

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?

Ruudbern · Apr 11, 2012

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

Heimsater · May 30, 2012

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

snowmann · Jun 20, 2012

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.

Alberto Pilat · Feb 12, 2013

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.

Alberto Pilat · Feb 15, 2013

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

heilm · Apr 26, 2013

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

ora-01480

Member²

Administrator

Member²

Member²

Member²

Administrator

Member²

Administrator

Member²

Member²

Administrator

Member

Administrator

Member²

Administrator

Member²

Administrator

Member²

Member²

Member²

Member²

Administrator

Member²

Administrator

Member²

Member²

Administrator

Member²

Member²

Administrator

Member²

Member²

Member

Administrator

Member

Member²

Member

Member

Member²

Member²

Member

Similar threads