Strange sql behavior – last character is removed in the Select statement, but actual data has different length

oracle

We have a table with a column name A with type nvarchar(23).

following query will always return 23 which means that the actual length of all records are 23.

select length(trim(req.A)), count(*)
  from tableName req
group by length(trim(req.A));

|length(trim(req.A))|count(*)| 
------------------------------
|23                 |1006    |

But when we select from this table with following query it behaves different and it seems that the last character is always removed in result Gridview in the pl/sql developer. 

select LENGTHB(req.A) lenb, length(req.A) len, req.* from tableName req
where req.A = 'NHBBBBB1398052635902235'; -- Note to the equal sign and the last charactar (5) of the where clause

the result is:

|lenb|len|          A           |
---------------------------------
|46  |23 |NHBBBBB139805263590223|

As you can see the last character (5) is removed in the select result.

Also when we rewrite previous query with to_char() or run it in sqlplus it works fine & return correct result with the length of 23 and (5) as the last character.

select LENGTHB(req.A) lenb, length(req.A) len, to_char(req.A) from tableName req
where req.A = 'NHBBBBB1398052635902235';

the result is:

|lenb|len|    to_char(req.A)     |
----------------------------------
|46  |23 |NHBBBBB1398052635902235|

Can you please explain whats happen!? Is this related to pl/sql configs? How to solve this?

Best Answer

It seems that the problem was an old version of PL/SQL Developer which our customers used. When they updated to the newer version, the problem has been solved.

Related Question