How to use Oracle Dump Function

最新推荐文章于 2024-07-27 19:21:39 发布

oracleerp

最新推荐文章于 2024-07-27 19:21:39 发布

阅读量459

点赞数

分类专栏： Oracle DB 文章标签： oracle function character timezone date database

本文链接：https://blog.youkuaiyun.com/oracleerp/article/details/2639587

版权

Oracle DB 专栏收录该内容

16 篇文章

订阅专栏

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,63

SELECT DUMP(last_name, 8, 3, 2) "OCTAL"
   FROM employees
   WHERE last_name = 'Hunold';

OCTAL
-------------------------------------------------------------------
Typ=1 Len=6: 156,157

SELECT 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   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> 
 
 
 
 
 
 

  
  
   
    查看更詳細的系統定義說明 
   
   [回复] 

  
  

  
  

   
   select text from dba_views where view_name = 'USER_TAB_COLS';