My Oracle Support Banner

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 9.2.0.1 to 12.1.0.2 [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,

Symptoms

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.

DIAGNOSTIC STEPS

  1. Get the actual output of the problem
  2. 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
  3. Execute the Problem Query and the Proper Query : Compare the two

For Example:

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:

 

 

Changes

None that were known:

Cause

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
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.