ORA-1480 error with Oracle 10gR1, UTF-8 database.

ldsandon · Jun 7, 2006

Database is set with "CHAR" semantic. I have a VARCHAR(2048) field where I try to write a string about 1700 characters long via a query with bind variables. I get an ORA-1480 error, "trailing null missing from STR bind value". The string is accepted if I do not use a bind variable. Is it an Oracle limitation, or is it a DOA issue?

ldsandon · Jun 7, 2006

Oooops. I forgot to add: DOA 4.0.7.1, Delphi 7 (all patches). Database (server and client) are 10.1.0.5.0

Marco Kalter · Jun 7, 2006

Did you set TOracleSession.BytesPerCharacter to bcAutoDetect?

ldsandon · Jun 8, 2006

Yes. The error does not happen always. It happens only when the string to be inserted is long.

Here it is what Oracle Monitor says:

Code:

00:01.141	00:00.015	Session.LogOn as
00:01.156	00:00.001	Query.Execute (1 record processed) select to_char(userenv('SESSIONID')) from dual
00:02.531	00:16.266	Query.Execute INSERT INTO  IKON_INFO.HTTP_REQUESTS(
  ID_HTTP_REQUEST,
  METHOD, URL, HOST, CONNECTION, HTTP_DATE,
  USER_AGENT, LAST_MODIFIED, IF_MODIFIED_SINCE,
  CONTENT_TYPE, CONTENT_LENGTH, CONTENT_ENCODING,
  CONTENT_LOCATION, HTTP_AUTHORIZATION, PROXY_AUTHORIZATION,
  HTTP_RANGE, REFERER, VIA, ELEMENT_ID,
  SESSION_ID, START_OFFSET, END_OFFSET )
  VALUES(
  IKON_INFO.SEQ_HTTP_REQUESTS.NEXTVAL,
  :METHOD , :URL , :HOST , :CONNECTION,
  :HTTP_DATE , :USER_AGENT , :LAST_MODIFIED,
  :IF_MODIFIED_SINCE , :CONTENT_TYPE,
  :CONTENT_LENGTH, :CONTENT_ENCODING,
  :CONTENT_LOCATION, :HTTP_AUTHORIZATION,
  :PROXY_AUTHORIZATION, :HTTP_RANGE, :REFERER,
  :VIA , :ELEMENT_ID, :SESSION_ID, :START_OFFSET,
  :END_OFFSET) RETURNING ID_HTTP_REQUEST
  INTO :ID_HTTP_REQUEST
00:02.547	00:00.001	Query.Execute (1 record processed) select length(chr(2000000000)) l4, length(chr(2000000)) l3,  length(chr(20000)) l2 from dual
00:18.813	00:00.015	Session.Commit
00:18.844	00:00.015	Session.LogOff

It looks the query to get the character len is executed after the insert query. Is correct?

ldsandon · Jun 8, 2006

Maybe this could help: I found out the TOracleSession.MaxVarchar returns 1000, and in TOracleQuery.BindVariable you have:

Code:

// Determine the maximum number of characters for a string or char variable
lBufSize := bufsize;
if (lBufType in [otString, otChar]) and (BufType <> otPLSQLString) and (lBufSize > Session.MaxVarchar + 1) then
  lBufSize := Session.MaxVarchar + 1;

This could truncate the string and result in an invalid UTF-8 string.
How did you calculate bytes per character? It looks DOA gest 4 bytes per character for an UTF-8 DB, but UTF-8 will use *up to* four bytes, but for most West European languages it will use one or two bytes.

Greg Stevenson · May 29, 2007

Marco,

We are encountering the same issue and it also exists in PL/SQL developer (and possibly SQL+), but not Toad or iSQL. Below is the support info from PL/SQL developer. (I know its not current but should but recent enough for this not to be an issue.) It does seem that something is pushing 4 bytes per char and truncating. I'm wondering if this is an OCI issue?
--Greg

PL/SQL Developer
Version 7.0.2.1076 (MBCS)
94.27456 - 100 user license
Windows XP version 6.0 (build 2600) Service Pack 2

Physical memory : 2,095,088 kB (892,120 available)
Paging file : 4,194,304 kB (4,046,972 available)
Virtual memory : 2,097,024 kB (2,010,928 available)

Parameters
C:\Program Files\PLSQL Developer\PLSQLDev.exe

Preferences
Session mode: Multi
OCI Library: C:\Oracle\product\10.1.0\Client_3\bin\oci.dll
Use OCI7: False

Preference Files
C:\Program Files\PLSQL Developer\Preferences\Default\Default.ini
C:\Program Files\PLSQL Developer\Preferences\gregs\default.ini

Plug-Ins

Aliases
DB3100
dvlp2920
DVLP3200
dvlp4100
DVLP51
...

Homes
OraClient10g_home3 (C:\Oracle\product\10.1.0\Client_3)
OraHome92 (C:\oracle\ora92)

DLLs
C:\Oracle\product\10.1.0\Client_3\bin\oci.dll

TNS File
C:\oracle\ora92\network\admin\tnsnames.ora

Using
Home: OraClient10g_home3
DLL: C:\Oracle\product\10.1.0\Client_3\bin\oci.dll
OCI: version 9.2
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Character size: 4 byte(s)

Marco Kalter · May 30, 2007

What is your client and server character set?

Greg Stevenson · May 30, 2007

We are checking to make sure the client and the server are both using UTF-8. SQL*Plus does not seem to have the issue so I don't think it's directly related to OCI. Changing the column from a varChar 4000 to a clob seems to fix the issue for PL/SQL Developer, but not for our client application. The DOA for source code we have is dated August 23, 2005, so I'm wondering if perhaps this was a bug that was fixed after that point in time. If so, it'll be a good reason to encourage iBASEt to update to the latest.
-- Greg

Greg Stevenson · May 30, 2007

We are checking to make sure the client and the server are both using UTF-8. SQL*Plus does not seem to have the issue so I don't think it's directly related to OCI. Changing the column from a varChar 4000 to a clob seems to fix the issue for PL/SQL Developer, but not for our client application. The DOA for source code we have is dated August 23, 2005, so I'm wondering if perhaps this was a bug that was fixed after that point in time. If so, it'll be a good reason to encourage iBASEt to update to the latest.
-- Greg

Greg Stevenson · May 30, 2007

Sorry for the double post, copy did not take and pasted old post that was still on the clipboard and uploaded without previewing it first.

Anyway, both client and server set to UTF-8. For now we put a switch in like PL/SQL Developer has, but are hoping for something more automatic that works with UTF-8. Is that doable?

--Greg

Marco Kalter · May 31, 2007

PL/SQL Developer 7.0.2 and Direct Oracle Access 4.0 do not support Unicode clients. You would need to use an explicit national character set on the client.

PL/SQL Developer 7.0.3 and later does support Unicode, and so does Direct Oracle Access 4.1 which will be available shortly.

Greg Stevenson · Jun 1, 2007

Marco,

There are apparently still issues even with 7.0.3. The item below is from one of our DBAs using 7.0.3 . It may just be a result of the VCL not having unicode support.
--Greg

Here are the steps to reproduce the ORA-1480 error on an Oracle Database using NLS_LANG AL32UTF8.

All of these steps were performed using pl/sql developer.

Create table al32utf8_test (c1 varchar2(4000));

Insert into al32utf8_test (c1)
Values ('ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*' );

Commit;

This creates a record with a length of 1439.

If I then use pl/sql developer to edit the data, when posting the change, the ORA-1480 error occurs.

If I use an update statement, then the update occurs without error.

update al32utf8_test
set c1 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*
ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*TESTUPDATE'
where rownum = 1

Marco Kalter · Jun 1, 2007

We'll check it out.

ORA-1480 error with Oracle 10gR1, UTF-8 database.

ldsandon

Member³

ldsandon

Member³

Marco Kalter

Administrator

ldsandon

Member³

ldsandon

Member³

Greg Stevenson

Member²

Marco Kalter

Administrator

Greg Stevenson

Member²

Greg Stevenson

Member²

Greg Stevenson

Member²

Marco Kalter

Administrator

Greg Stevenson

Member²

Marco Kalter

Administrator

Similar threads