Single char select from dual returns additional char

Toad for Oracle
1

select 'x' from dual returns additional char: xણ -> error
select 'xx' from dual returns correctly: xx
select col1 from tbl1 returns correct result
Toad for Oracle 14.0.75.662

2

Is it the same as described here? Data corruption in result of SELECT statement, beta14.0.51.637

I was able to reproduce that at one time, and a solution is coming, but for some reason I can't reproduce it now.

3

Hi John, yes, I didn't find that one.
You have to select a single char like 'x' from dual.
select 1 will work, select more than one char ('aa') will work, too.
"Data corruption" is a bit harsh IMHO. It's just the display in data grid.
dbms output works fine.
Best regards, Hans

4

I'm not sure what's going on with that.

For me, it is working correctly. This is with 19c database and 12cR2 client.

image
image1042×666 48.9 KB

5

plz, try 64-bit version of Toad for Oracle. I just installed today current instant client with Toad for Oracle. All 64 bit. Ora DB 19 UTF-8.

6

I am not able to reproduce this right now (in 32 or 64 bit), but for some reason I can also no longer reproduce the problem that I gave the link for above either. I was able to reproduce that one earlier. I'm not sure why I can't anymore.

When I could reproduce it, I reported it to our 3rd party Oracle access component. They provided an update that seemed to fix it, which I am planning to update to for the next Toad version.

What is your setting for cursor_sharing?

-John

7

SELECT *
FROM v$parameter
WHERE name = 'cursor_sharing';
=>
3458 cursor_sharing 2 FORCE FORCE EXACT FALSE TRUE IMMEDIATE TRUE TRUE FALSE FALSE FALSE FALSE cursor sharing mode 3355675594 0

Again: This behavior occurs in data grid only. Something like
DECLARE
res VARCHAR2(10);
BEGIN
SELECT 'x' INTO res FROM dual;
dbms_output.put_line(res);
END;
/
will return correct result. Is there eventually some initialization not working properly?

8

some additional input:
Oracle DB 19.4.0.0.0 with WE8ISO8859P1, cursor_sharing EXACT: correct result !
Oracle DB 12.1.0.2.0 with WE8ISO8859P15, cursor_sharing FORCE: same additional char as described above
originally detected on 19.7.0.0.0 with AL32UTF8, cursor_sharing: FORCE
hope, this helps.
BTW: Eventually I won't be able to respond again before end of this week.

9

selecting several strings from dual results in undeterministic, funny results:
SELECT 'abcd', 'efgh', 'Приве́т!' FROM dual;
=>
abcd䆀ણ efghersion="32" parser="5.362" f Приве́т!000000" source_encoding=

abcd䆀ણ efgh Приве́т!䖠Я
abcd䩀ણ efgh彴慮敭‬楦獲⵴慮敭‬楦獲⹴慮敭‬楦獲湴浡ⱥ映物瑳Ⱒ਍†∠ Приве́т!⡢楦獲孴张屜⸭㽝㼨渺浡⥥⤿屜≢ബ †䔢慮汢摥㨢琠

Actually, queries on other connections within same TOAD client seem to effect this query.

10

Hi Hans.

This is looking more and more like like the same bug that was reported here Data corruption in result of SELECT statement, beta14.0.51.637. We should have a fix out for it sometime in January.

-John

11

Hi John, that's perfectly fine for me. Thx.
Best regards, Hans

12

We changed cursor_sharing to 'EXACT' (for some other, slightly related issue). Now the results are correct.

1 Like