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 AUGUST 09, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 12.1.0.2 [Release 9.2 to 12.1]
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:


SQL> select 'ANY_WORD' from dual;

'ANY_WOR
--------             <<< notice that the top part of the output is truncated AND the number of under_bars -------- is different then the next query
ANY_WORD


--> they stated that this was the expected outcome: However what they were seeing in the broken configuration was the following:

SQL> select 'ANY_WORD' from dual;

'ANY_WORD'
-----------------------------------------
ANY_WORD 


Here is another simple example that I created

 "Bad Output"

SQL> select 'abc' from dual;

'ABC'                                            
----------------------------------------- <-notice the length of dashes used in the column header name
abc 

"Good Output"

SQL> select 'abc' from dual;

'AB    << Here String is only 3-bytes long and truncates the ..c' from 'abc'
---    << Format of the Dashes is also altered --- and is only 3 bytes of dashes!
abc 

 

In other words: The result set output of  _abc_ appears exactly the same in both configurations, but the SQLPLUS  formatting seen for the column label and the associated dashes used to format the query output are not the same

In order to check to see if this problem was as they stated a conversion from VARCHAR2 which is variable width for data vs. CHAR which will pad the results for the entire length we can use the DUMP command.

SQL> select dump('ANY_WORD',1010), 'ANY_WORD' from dual;

DUMP('ANY_WORD',1010) 'ANY_WOR
----------------------------------------------------------- --------
Typ=96 Len=8 CharacterSet=US7ASCII: 65,78,89,95,87,79,82,68 ANY_WORD


An example of the 'Bad Configuration' output showed that the datatype was no different in the good vs bad configuration:

SQL> select dump('ANY_WORD',1010), 'ANY_WORD' from dual;

DUMP('ANY_WORD',1010)
--------------------------------------------------------------------------------
'ANY_WORD'
--------------------------------                     
Typ=96 Len=8 CharacterSet=AL32UTF8: 65,78,89,95,87,79,82,68
ANY_WORD 


Once again however we see the formating of the output as changed

Last: a simple representation of the problem revealed that we did not even have to use a SQL queryto get the unexpected formatting problem:    

First representation

SQL> sho parameter cursor

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing string
SIMILAR
cursor_space_for_time boolean
FALSE
open_cursors integer
300
session_cached_cursors integer
20



Second representation

 


SQL> sho parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                        string         EXACT
cursor_space_for_time                 boolean        FALSE
open_cursors                          integer        300
session_cached_cursors                integer        20

 

 

Changes

None that were known:

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms