SQL Query Format Differs Using the Same Data and Objects if Executed in a Different Database including a simple SELECT from DUAL - Due to Using a Function with Unicode vs. Single-Byte Changes SQL Column Header Lengths
(Doc ID 1086519.1)
Last updated on DECEMBER 18, 2019
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 to 126.96.36.199 [Release 9.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 09-Aug-2017***
SQLPlus, Format, column header, Unicode, UTF8,AL32UTF8,
SQL Header seen from query is no longer the same length using the exact same query and data set.
Here is an example of how the problem was described however which is misleading
"...We are getting a different response of the same query in our production database compared to development/test database . The literal value is returned as CHAR when expecting a VARCHAR..."
This description is incorrect: To help understand the actual problem it is best to actuall view the problem from the user's perspective.
- Get the actual output of the problem
- When the problem is described as difference in datatypes such as CHAR vs VARCHAR, we can use the DUMP command to diagnose the problem to look at the datatype, datatype length in bytes and the validity or differences between the two result sets
- Execute the Problem Query and the Proper Query : Compare the two
This user stated that even a simple SELECT 'ANY_WORD' from DUAL; -- would show the problem between their two configurations
Let's try the following to show the problem:
None that were known:
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document