1

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?

  • What happens when you run the queries in sqlplus? – Colin 't Hart Aug 18 '19 at 12:03
  • @Colin'tHart unfortunately, the db is on our customer environment and we don't have direct access to it. They denied to run this query in sqlplus. – Ziaa Aug 18 '19 at 13:58
  • What is the datatype of the column in question? What is the database character set? – mustaccio Aug 18 '19 at 14:07
  • What if you try this? ``` select LENGTHB(req.A) lenb, length(req.A) len, req.* from tableName req where req.A = N'NHBBBBB1398052635902235'; ``` A literal in between quotes is created as VARCHAR2. If you put the "N" before the quote, it will be created as NVARCHAR2. – Massimo Pasquini Aug 24 '19 at 17:09
2

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.