How to use Oracle Dump Function

DUMP returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set.

The syntax is:

DUMP(expr[,return_fmt[,start_position[,length]]])

The argument return_fmt specifies the format of the return value and can have any of the following values:

• 8 returns result in octal notation.
• 10 returns result in decimal notation.
• 16 returns result in hexadecimal notation.
• 17 returns result as single characters.

By default, the return value contains no character set information. To retrieve the character set name of expr, specify any of the preceding format values, plus 1000. For example, a return_fmt of 1008 returns the result in octal, plus provides the character set name of expr.

The arguments start_position and length combine to determine which portion of the internal representation to return. The default is to return the entire internal representation in decimal notation.

If expr is null, then this function returns a null.

Examples

The following examples show how to extract dump information from a string expression and a column:

SELECT DUMP('abc', 1016)   FROM DUAL;DUMP('ABC',1016)------------------------------------------Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63SELECT DUMP(last_name, 8, 3, 2) "OCTAL"   FROM employees   WHERE last_name = 'Hunold';OCTAL-------------------------------------------------------------------Typ=1 Len=6: 156,157SELECT DUMP(last_name, 10, 3, 2) "ASCII"   FROM employees   WHERE last_name = 'Hunold';ASCII---------------------------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

我們知道可能用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   72 number    78 long    712 date    723 raw    724 long raw   769 rowid    796 char    7112 clob    8113 blob    8114 bfile    8180 timestamp   9i181 timestamp with timezone  9i182 interval year to month  9i183 interval day to second  9i208 urowid    8i231 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----------SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER
14 rows selected
SQL> select ename,dump(ename) "dump" from emp;
ENAME      dump---------- --------------------------------------------------------------------------------SMITH      Typ=1 Len=5: 83,77,73,84,72ALLEN      Typ=1 Len=5: 65,76,76,69,78WARD       Typ=1 Len=4: 87,65,82,68JONES      Typ=1 Len=5: 74,79,78,69,83MARTIN     Typ=1 Len=6: 77,65,82,84,73,78BLAKE      Typ=1 Len=5: 66,76,65,75,69CLARK      Typ=1 Len=5: 67,76,65,82,75SCOTT      Typ=1 Len=5: 83,67,79,84,84KING       Typ=1 Len=4: 75,73,78,71TURNER     Typ=1 Len=6: 84,85,82,78,69,82ADAMS      Typ=1 Len=5: 65,68,65,77,83JAMES      Typ=1 Len=5: 74,65,77,69,83FORD       Typ=1 Len=4: 70,79,82,68MILLER     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,HALLEN      Typ=1 Len=5: A,L,L,E,NWARD       Typ=1 Len=4: W,A,R,DJONES      Typ=1 Len=5: J,O,N,E,SMARTIN     Typ=1 Len=6: M,A,R,T,I,NBLAKE      Typ=1 Len=5: B,L,A,K,ECLARK      Typ=1 Len=5: C,L,A,R,KSCOTT      Typ=1 Len=5: S,C,O,T,TKING       Typ=1 Len=4: K,I,N,GTURNER     Typ=1 Len=6: T,U,R,N,E,RADAMS      Typ=1 Len=5: A,D,A,M,SJAMES      Typ=1 Len=5: J,A,M,E,SFORD       Typ=1 Len=4: F,O,R,DMILLER     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,HALLEN      Typ=1 Len=5: L,L,E,NWARD       Typ=1 Len=4: A,R,DJONES      Typ=1 Len=5: O,N,E,SMARTIN     Typ=1 Len=6: A,R,T,IBLAKE      Typ=1 Len=5: L,A,K,ECLARK      Typ=1 Len=5: L,A,R,KSCOTT      Typ=1 Len=5: C,O,T,TKING       Typ=1 Len=4: I,N,GTURNER     Typ=1 Len=6: U,R,N,EADAMS      Typ=1 Len=5: D,A,M,SJAMES      Typ=1 Len=5: A,M,E,SFORD       Typ=1 Len=4: O,R,DMILLER     Typ=1 Len=6: I,L,L,E
14 rows selected
SQL> 
 查看更詳細的系統定義說明 [回复] 
select text from dba_views where view_name = 'USER_TAB_COLS';