Usage of the Oracledump Function
I. Standard Function Format:
DUMP (expr [, return_fmt [, start_position] [, length])
There are four basic parameters. The minimum value is 0. If no parameter exists, null is directly returned. The other three parameters also have their own default values:
Expr: this parameter is the expression for analysis (numbers, strings, etc., can be values of various types)
Return_fmt: the format of the returned parameter. It can be used in five ways:
1) 8: return the result value in octal format.
2) 10: return the value of the result in 10 notation (default)
3) 16: return the result value in hexadecimal notation.
4) 17: return the result value in the form of a single character
5) 1000: the preceding four types plus 1000 indicate adding the current character set to the return value.
Start_position: start to return the character position
Length: the length of the characters to be returned.
Ii. Example
SYS @ tsid> select dump ('abc') from dual;
DUMP ('abc ')
----------------------
Typ = 96 Len = 3: 97,98, 99
SYS @ tsid> select dump ('abc', 16) from dual;
DUMP ('abc', 16)
----------------------
Typ = 96 Len = 3: 61,62, 63
SYS @ tsid> select dump ('abc', 1016) from dual;
DUMP ('abc ', 1016)
--------------------------------------------
Typ = 96 Len = 3 CharacterSet = ZHS16GBK: 61,62, 63
SYS @ tsid> select dump ('abc', 17,2, 2) from dual;
DUMP ('abc', 17,2, 2
-----------------
Typ = 96 Len = 3: B, c
The result format is generally similar to: Typ = 96 Len = 3 [CharacterSet = ZHS16GBK]: 61,62, 63
1. type
Typ indicates the type of the current expr value. For example, 2 indicates NUMBER and 96 indicates CHAR.
CODE TYP
-----------------------------------
1 VARCHAR2
2 NUMBER
8 LONG
12 DATE
23 RAW
24 LONG RAW
69 ROWID
96 CHAR
112 CLOB
113 BLOB
114 BFILE
180 TIMESTAMP
181 TIMESTAMP WITH TIMEZONE
182 INTERVAL YEAR TO MONTH
183 INTERVAL DAY TO SECOND
208 UROWID
231 TIMESTAMP WITH LOCAL TIMEZONE
2. len
Len indicates the number of bytes occupied by the value.
For Chinese characters, ZHS16GBK requires two bytes to encode a Chinese character, and UTF8 requires three bytes.
SQL> select dump ('duodu', 1010) from dual;
DUMP ('duoduo', 1010)
-------------------------------------------------------
Typ = 96 Len = 6 CharacterSet = UTF8: 229,164,154,229,164,154
SQL> select dump ('duodu', 1010) from dual;
DUMP ('duoduo', 1010)
---------------------------------------------------
Typ = 96 Len = 4 CharacterSet = ZHS16GBK: 182,224,182,224
3. Value
Specific storage value. The returned value is the expr value stored in Oracle internally. A non-Chinese character string can be understood as its ASCII code. Example:
SYS @ tsid> select dump ('a =? 5') from dual;
DUMP ('a =? 5 ')
-------------------------
Typ = 96 Len = 4: 97,61, 63,53
SYS @ tsid> select chr (97), chr (61), chr (63), chr (53) from dual;
C
----
A =? 5
SYS @ tsid> select ascii ('A'), ascii ('='), ascii ('? '), Ascii ('5') from dual;
ASCII ('A') ASCII ('=') ASCII ('? ') ASCII ('5 ')
----------------------------------------
97 61 63 53
ASCII code table:
ASCII Value |
Control characters |
ASCII Value |
Control characters |
ASCII Value |
Control characters |
ASCII Value |
Control characters |
0 |
NUT |
32 |
(Space) |
64 |
@ |
96 |
, |
1 |
SOH |
33 |
! |
65 |
A |
97 |
A |
2 |
STX |
34 |
" |
66 |
B |
98 |
B |
3 |
ETX |
35 |
# |
67 |
C |
99 |
C |
4 |
EOT |
36 |
$ |
68 |
D |
100 |
D |
5 |
ENQ |
37 |
% |
69 |
E |
101 |
E |
6 |
ACK |
38 |
& |
70 |
F |
102 |
F |
7 |
BEL |
39 |
, |
71 |
G |
103 |
G |
8 |
BS |
40 |
( |
72 |
H |
104 |
H |
9 |
HT |
41 |
) |
73 |
I |
105 |
I |
10 |
LF |
42 |
* |
74 |
J |
106 |
J |
11 |
VT |
43 |
+ |
75 |
K |
107 |
K |
12 |
FF |
44 |
, |
76 |
L |
108 |
L |
13 |
CR |
45 |
- |
77 |
M |
109 |
M |
14 |
SO |
46 |
. |
78 |
N |
110 |
N |
15 |
SI |
47 |
/ |
79 |
O |
111 |
O |
16 |
DLE |
48 |
0 |
80 |
P |
112 |
P |
17 |
DCI |
49 |
1 |
81 |
Q |
113 |
Q |
18 |
DC2 |
50 |
2 |
82 |
R |
114 |
R |
19 |
DC3 |
51 |
3 |
83 |
X |
115 |
S |
20 |
DC4 |
52 |
4 |
84 |
T |
116 |
T |
21 |
NAK |
53 |
5 |
85 |
U |
117 |
U |
22 |
SYN |
54 |
6 |
86 |
V |
118 |
V |
23 |
TB |
55 |
7 |
87 |
W |
119 |
W |
24 |
CAN |
56 |
8 |
88 |
X |
120 |
X |
25 |
EM |
57 |
9 |
89 |
Y |
121 |
Y |
26 |
SUB |
58 |
: |
90 |
Z |
122 |
Z |
27 |
ESC |
59 |
; |
91 |
[ |
123 |
{ |
28 |
FS |
60 |
< |
92 |
/ |
124 |
| |
29 |
GS |
61 |
= |
93 |
] |
125 |
} |
30 |
RS |
62 |
> |
94 |
^ |
126 |
~ |
31 |
US |
63 |
? |
95 |
- |
127 |
DEL |
The storage of numbers is not as simple as character, but an Oracle algorithm.