CODEWITHSUNDEEP
sqloracle-databaseoracle11g
DerIngo
DerIngo

Reputation: 11

Oracle whitespaces bug

I have noticed a weird bug in our Oracle database as we are moving from one database (Oracle 11.2.0.3.0) to another database (Oracle 11.2.4.0). Database servers are in different data center with different operating systems and support teams. So the version is not the only thing that has changed.

So, this is the bug I am facing: When I execute this little SQL statement I get different results:

SELECT 'hello' FROM DUAL;

On the old database the output is: "hello"
On the new database the output is: "hello "

Where are these whitespaces coming from?

UPDATE:

I am using TOAD for Oracle in version 12.10.0.30.

I have checked this parameters for both databases, but could not find any differences (except, of course, the version):

SELECT * FROM V$VERSION;
SELECT * FROM v$nls_parameters;
SELECT * FROM nls_session_parameters;
SELECT * FROM nls_instance_parameters;
SELECT * FROM nls_database_parameters;

Then I have checked this and found no differences:

DESC DUAL;

The next test I did was:

SELECT dummy FROM DUAL;

And the result for both databases was:X

Next test:

SELECT '|' || 'hello' || '|' FROM DUAL;

Result old database:"|hello|"
Result new database:"|hello| "

And another test:

SELECT '|' || dummy || '|' || 'Hello' || '|' FROM DUAL;
SELECT '|' || dummy || '|' FROM DUAL;
SELECT '|' || 'Hello' || '|' FROM DUAL;

And the results:
"|X|Hello|"
"|X|"
"|Hello| "
Interestingly it works for the first SQL, but the third one is filled up with whitespaces again.

SQL*Plus

I have installed SQL*Plus on the same laptop TOAD is running.
Old database:

SQL> select dummy from dual;
D
-
X
SQL> select 'dummy' from dual;
'DUMM
-----
dummy
SQL> SELECT dump('hello') FROM DUAL;
DUMP('HELLO')
---------------------------------
Typ=96 Len=5: 104,101,108,108,111

New database:

SQL> select dummy from dual;
D
-
X
SQL> select 'dummy' from dual;
'DUMMY'
--------------------------------
dummy
SQL> SELECT dump('hello') FROM DUAL;
DUMP('HELLO')
--------------------------------------------------------------------------------
Typ=96 Len=5: 104,101,108,108,111

Solution

DB Parameter *.cursor_sharing was "EXACT" on the old database and "FORCE" on the new database. So we will have to change it to "EXCACT" too.

Upvotes: 1

Views: 334

Answers (2)

tbone
tbone

Reputation: 15473

Just messing around with this, and found that if you mess with the NLS_LANG settings on the client (from linux, probably the same for windows) I get different results (at least as it displays):

  1. No NLS_LANG env variable set:

SQL> select 'hello' from dual;

'HELL
-----
hello
  1. export NLS_LANG=American_America.UTF8

SQL> select 'hello' from dual;

'HELLO'
---------------
hello

I'm guessing that it has to do with multibyte charset, but note that the length function for both still returns 5.

So check on your new setup to make sure your NLS setup is correct.

Hope that helps.

EDIT:

I logged into Oracle to run your liveSQL test, and I do NOT get the spacing you see. I think you may be opening the csv in Excel after downloading. I opened in a text editor (V) and dumped the hex for the example you gave that supposedly has padding, and it does not:

00000000  27 7C 27 7C 7C 27 48 45 4C 4C 4F 27 7C 7C 27 7C    '|'||'HELLO'||'|
00000010  27 0A 7C 68 65 6C 6C 6F 7C 0A                      '.|hello|.

The dots above are newlines (0A). There is no whitespace padding going on that I can find.

Your issue isn't reproducible (at least not in my environment or from liveSQL).

Upvotes: 0

Mark Stewart
Mark Stewart

Reputation: 2098

Somehow the literal string hello is being interpreted as a CHAR(26) data type (which would be padded with spaces to be 26 characters long) instead of a VARCHAR2(4000) data type. Since you are selected from the DUAL table, it must be something strange in the environment variables or or tools you are issuing the query from.

Upvotes: 1

Related Questions