转载：http://vongates.itpub.net/post/2553/17275

作者：vongates

我們知道可能用dump這個函數來查看表中某列在datafile中的存儲內容，它的標准格式為：DUMP(expr[,number_format[,start_position][,length]]) 它將返回一個包含expr內部表示信息的varchar2值

我們常用的函數格式為：dump(col_name,8|10|16|17)其中的8|10|16|17為number_formant的取值，分別指八進制|十進制|十六進制|單字符 其中10為default的值，實例

編碼 數據類型 oracle版本
1 varchar2 7
2 number 7
8 long 7
12 date 7
23 raw 7
24 long raw 7
69 rowid 7
96 char 7
112 clob 8
113 blob 8
114 bfile 8
180 timestamp 9i
181 timestamp with timezone 9i
182 interval year to month 9i
183 interval day to second 9i
208 urowid 8i
231 timestamp with local timezone 9i

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0
Connected as scott

SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) Y
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y

SQL> select ename from emp;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected

SQL> select ename,dump(ename) "dump" from emp;

ENAME dump
---------- --------------------------------------------------------------------------------
SMITH Typ=1 Len=5: 83,77,73,84,72
ALLEN Typ=1 Len=5: 65,76,76,69,78
WARD Typ=1 Len=4: 87,65,82,68
JONES Typ=1 Len=5: 74,79,78,69,83
MARTIN Typ=1 Len=6: 77,65,82,84,73,78
BLAKE Typ=1 Len=5: 66,76,65,75,69
CLARK Typ=1 Len=5: 67,76,65,82,75
SCOTT Typ=1 Len=5: 83,67,79,84,84
KING Typ=1 Len=4: 75,73,78,71
TURNER Typ=1 Len=6: 84,85,82,78,69,82
ADAMS Typ=1 Len=5: 65,68,65,77,83
JAMES Typ=1 Len=5: 74,65,77,69,83
FORD Typ=1 Len=4: 70,79,82,68
MILLER Typ=1 Len=6: 77,73,76,76,69,82

14 rows selected

SQL> select ename,dump(ename,17) "dump" from emp;

ENAME dump
---------- --------------------------------------------------------------------------------
SMITH Typ=1 Len=5: S,M,I,T,H
ALLEN Typ=1 Len=5: A,L,L,E,N
WARD Typ=1 Len=4: W,A,R,D
JONES Typ=1 Len=5: J,O,N,E,S
MARTIN Typ=1 Len=6: M,A,R,T,I,N
BLAKE Typ=1 Len=5: B,L,A,K,E
CLARK Typ=1 Len=5: C,L,A,R,K
SCOTT Typ=1 Len=5: S,C,O,T,T
KING Typ=1 Len=4: K,I,N,G
TURNER Typ=1 Len=6: T,U,R,N,E,R
ADAMS Typ=1 Len=5: A,D,A,M,S
JAMES Typ=1 Len=5: J,A,M,E,S
FORD Typ=1 Len=4: F,O,R,D
MILLER Typ=1 Len=6: M,I,L,L,E,R

14 rows selected

SQL> select ename,dump(ename,17,2,4) "dump" from emp;

ENAME dump
---------- --------------------------------------------------------------------------------
SMITH Typ=1 Len=5: M,I,T,H
ALLEN Typ=1 Len=5: L,L,E,N
WARD Typ=1 Len=4: A,R,D
JONES Typ=1 Len=5: O,N,E,S
MARTIN Typ=1 Len=6: A,R,T,I
BLAKE Typ=1 Len=5: L,A,K,E
CLARK Typ=1 Len=5: L,A,R,K
SCOTT Typ=1 Len=5: C,O,T,T
KING Typ=1 Len=4: I,N,G
TURNER Typ=1 Len=6: U,R,N,E
ADAMS Typ=1 Len=5: D,A,M,S
JAMES Typ=1 Len=5: A,M,E,S
FORD Typ=1 Len=4: O,R,D
MILLER Typ=1 Len=6: I,L,L,E

14 rows selected

SQL>