chr(0)

[deniskeen]

Hi!

If a varchar2 column in a table contains chr(0)
symbol then all the data starting from the
symbol's position till the end of the column's
value is invisible when selecting in SQL window.
(Oracle 9.2.0.4; PL/SQL Developer 6.0.0.816)

PL/SQL Developer's SQL window:

create table t (s varchar2(1000));
insert into t values ('qwe'||chr(0)||'rty');
select * from t;
-- qwe
select dump(s) from t;
drop table t;
-- Typ=1 Len=7: 113,119,101,0,114,116,121

SQL*Plus:

SQL> create table t (s varchar2(1000));
Table created.

SQL> insert into t values ('qwe'||chr(0)||'rty');
1 row created.

SQL> select * from t;
S
-------------------------------------------------
qwe rty

SQL> select dump(s) from t;
DUMP(S)
-------------------------------------------------
Typ=1 Len=7: 113,119,101,0,114,116,121

Denis

 

[Marco Kalter]

The 0 character is currently seen as an end-of-string character (c-style zero-terminated string). We're looking into a solution.

 

[Stian]

Have there been any progress on this?

I'm running 7.1.5.1398 and I use the fnd_log_messages table a lot these days. in text_message it separates messages by chr(0). I have to replace all instances with something else. Not a big hassle when I remember to do it. But it is kind of annoying that the character is invisible.

I just spent two hours debugging something when I discovered a chr(0) tagging along my string. It shows as space in sqlplus or a square in SQL Developer.

Regards
Stian

 

[Marco Kalter]

We don't have a solution yet. It's a bit of a low-level issue, and will have a lot of impact. It's still on the to-do list though.