Database Reference
In-Depth Information
SQL DUMp FUNCtION
the Oracle sQL
DUMP
function allows you to display the datatype code, length in bytes, and the internal
representation of a data value (also optionally the character set name). Its syntax is as follows:
DUMP( expression [,return_format] [,start_position] [,length] )
the default
return_format
is 10 (decimal) and can be any of the following: 8, 10, 16, 17, 1008, 1010, 1016, or
1017. Where 8 is octal notation, 10 is decimal, 16 is hexadecimal, 17 is single characters, 1008 is octal with the
character set name, 1010 is decimal with character set name, 1016 is hexadecimal with the character set name,
and 1017 is single characters with the character set name. the following example dumps information regarding
the “a” character:
EODA@ORA12CR1> select dump('a'), dump('a',8), dump('a',16) from dual;
DUMP('A') DUMP('A',8) DUMP('A',16)
---------------- ----------------- ----------------
Typ=96 Len=1: 97 Typ=96 Len=1: 141 Typ=96 Len=1: 61
Where 97, 141, and 61 are the corresponding asCII codes for the “a” character in decimal, octal, and hexadecimal
notation. the returned datatype code of
Typ=96
indicates a
CHAR datatype
(see the
Oracle Database SQL
Language Reference
manual for a complete list of Oracle datatype codes and meanings).
This demonstrates the immediate impact of an environment with a heterogeneous character set, whereby the
clients and database use different NLS settings. It is something to be aware of because it comes into play in many
circumstances. For example, if the DBA uses the deprecated legacy
EXP
tool to extract information, he may observe the
following warning:
[tkyte@desktop tkyte] exp userid=eoda tables=t
Export: Release 12.1.0.1.0 - Production on Thu Jan 9 16:11:24 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path
...
Such warnings should be treated very seriously. If you were exporting this table with the goal of dropping the
table and then using
IMP
to re-create it, you would find that
all
of your data in that table was now lowly 7-bit data!
Beware the unintentional character set conversion.
