Execute a SELECT statement generates corrupt data in the result grid. Running the same SELECT in SQL*Plus or in Toad 13.3GA works fine.
The corrupt data is a constant value in the SELECT statement.
Can you give me a statement to reproduce this with?
When I try a constant value (select 1 from dual, for example) it works OK for me.
I'm using a 64bit version Toad. Oracle Server is 12.2 64 bit.
I can send you the 'normal' SELECT which generated the original error. I've already run the same statement in SQL*Plus and spool the output into a log file. The result in the latter tool was ok.
Oh, I've seen that with older Oracle clients. What client version are you using?
I'm using a locally installed Oracle Server (12.2.0.1 ) as the client.
Hm, I'm not sure why that's happening.
It works ok for me on a windows 10 server running Oracle 12.2.0.1
I made some investigation and got the following results:
- cast as char cause a corrupt result
SELECT '1' AS col1,
'TEST' AS col1,
cast ('1' AS CHAR (9)) AS col1_aschar,
cast ('TEST' AS CHAR (9)) AS col2_aschar
FROM dual ;
- cast as char/varchar2, but at least 80 length, the result is ok.
SELECT '1' AS col1,
'TEST' AS col1,
cast ('1' AS CHAR (80)) AS col1_aschar,
cast ('TEST' AS CHAR (80)) AS col2_aschar,
cast ('1' AS VARCHAR2 (80)) AS col1_asvarchar,
cast ('TEST' AS varCHAR2 (80)) AS col2_asvarchar
FROM dual
Can you reproduce the problem in your beta version?
What's the difference between 13.3 and 14beta versions in that situation? I was unable to reproduce the data corruption in the GA version.
For me, it works OK on both 13.3 and the Beta (both 64 bit).
There was an update to our Oracle access component between 13.3 and 14.0, but so far I haven't heard of any problems with it. There will be another beta coming out soon (today or tomorrow). I don't think anything has changed in it that would affect this, but fingers crossed.
This is windows 10, if it matters.
Does the problem happen if you connect to that database from another PC?
I'll check this tomorrow, because I'm currently using a laptop at home. I connect to DB with Cisco Anyconnect VPN.
I started the locally installed Oracle Server (the same 12.2.0.1) and made the same test in it. There was no error at all with the same SELECT statements.
The charset of databases is EE8ISO8859P2.
My Windows 10 is Version 10.0.19041.546
Can I ask one more thing? There is one difference between the databases. If I describe the following SELECT statement in both database (with Ctrl+F9) I get different result:
SELECT '1' AS col1,
'TEST' AS col1,
cast ('1' AS VARCHAR2 (8)) AS col1_aschar,
cast ('TEST' AS VARCHAR2 (8)) AS col2_aschar
FROM dual;
--
-- this is from database where corruption has occured
COL1 CHAR (32)
COL1_1 CHAR (32)
COL1_ASCHAR VARCHAR2 (8)
COL2_ASCHAR VARCHAR2 (8)
--
-- this is from database where result is ok
COL1 CHAR (1)
COL1_1 CHAR (4)
COL1_ASCHAR VARCHAR2 (8)
COL2_ASCHAR VARCHAR2 (8)
What is your result in your DB?
I see this coming from 13.3 and 14.0:
COL1 CHAR (1)
COL1_1 CHAR (4)
COL1_ASCHAR VARCHAR2 (8)
COL2_ASCHAR VARCHAR2 (8)
Windows 10 Enterprise, version 2004, build 19041.508 here.
My Charset is AL32UTF8 but I think I have a non-unicode DB also. Will try that next.
Every time I've seen this happen in the past, it's been solved by installing a newer client, but I've never seen it happen with a 12c client. It was always 10g or older. And the fact that it's happening on beta but not 13.3 is weird.
Edit: My only non-unicode DB is 12.1 with WE8ISO8859P15.
That works OK too.
I can try to match your env better tomorrow. (let me know what language windows is set up to...maybe that matters.)
I have reproduced this problem in two different databases.
Neither character set, nor NLS_LANG seem to matter.
The key to reproducing this is simply to run the following SQL, in order:
alter session set "_adjust_literal_replacement"=FALSE;
alter session set cursor_sharing = 'FORCE';
SELECT '1' AS col1,
'TEST' AS col2,
cast ('1' AS CHAR (8)) AS col1_aschar,
cast ('TEST' AS CHAR (8)) AS col2_aschar
FROM dual;
I have reproduced it in a demo app and reported the problem to our 3rd party Oracle access component vendor.
I have also logged this in our internal bug tracking system.
Hopefully we'll have a solution soon.
1 Like
This should be fixed in the current beta.