Usage of the Oracledump Function

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.