一、ASCII码与字符相互转换
ASCII(x) gets the ASCII value of the character x，CHR() and ASCII() have the opposite effect.例子：
SELECT ASCII('a'), ASCII('A'),ASCII('0') from dual
结果：
ASCII('a') ASCII('A') ASCII('0')
97 65 48
------------------------------------------------
例子：
select chr(65) from dual
结果：
CHR(65)
A
-------------------------------------------------------------
值得注意的是：Ascii gives the ASCII value of the first character of a string。也即是说他只对一个字符串中的第一个字符起作用，你弄多长的字符串放进去它也只认第一个字符。
原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://wanqiufeng.blog.51cto.com/409430/438193
二、dump函数与utl_raw
函数式：
DUMP(expr[,return_fmt[,start_position][,length]])
基本参数时4个，最少可以填的参数是0个。当完全没有参数时，直接返回null。另外3个参数也都有各自的默认值：
expr：这个参数是要进行分析的表达式（数字或字符串等，可以是各个类型的值）
return_fmt：指返回参数的格式，有5种用法：
1）8：以8进制返回结果的值
2）10：以10进制返回结果的值（默认）
3）16：以16进制返回结果的值
4）17：以单字符的形式返回结果的值
5）1000：以上4种加上1000，表示在返回值中加上当前字符集
start_position：开始进行返回的字符位置
length：需要返回的字符长度
示例
sql> select dump('abc') from dual;
DUMP('ABC')
----------------------
Typ=96 Len=3: 97,98,99
sql> select dump('abc',16) from dual;
DUMP('ABC',16)
----------------------
Typ=96 Len=3: 61,62,63
sql> select dump('abc',1016) from dual;
DUMP('ABC',1016)
--------------------------------------------
Typ=96 Len=3 CharacterSet=ZHS16GBK: 61,62,63
sql> select dump('abc',17,2,2) from dual;
DUMP('ABC',17,2,2
-----------------
Typ=96 Len=3: b,c
结果的格式一般都是类似：Typ=96 Len=3 [CharacterSet=ZHS16GBK]: 61,62,63
type
typ表示当前的expr值的类型。如：2表示NUMBER，96表示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
len
len表示该值所占用的字节数。
对于汉字，ZHS16GBK编码一个汉字需要2个字节，UTF8需要3个字节。
Value
具体的存储值。返回的数值就是Oracle在自己内部对前面的这个expr值得存储形式。对于非汉字的普通字符串，可以理解为就是它的ASCII码。
SQL> select to_number('3230','xxxx')from dual;
TO_NUMBER('3230','XXXX')
------------------------
12848
SQL> select to_number('3430','xxxx')from dual;
TO_NUMBER('3430','XXXX')
------------------------
13360
SQL> select to_number('3036','xxxx')from dual;
TO_NUMBER('3036','XXXX')
------------------------
12342
SQL>
SQL> select chr(12848)from dual;
CHR(12848)
----------
20
SQL> select chr(13360)from dual;
CHR(13360)
----------
40
SQL> select chr(12342)from dual;
CHR(12342)
----------
06
将CHR(12848)+CHR(13360)+CHR(12342)=204006
utl_raw.cast_to_xxx()作为dump的逆函数
sql>select dump('201201',16) from dual;
dump('201201',16)
---------------------------------------------------------
Typ=96 Len=6: 32,30,31,32,30,31
sql>select utl_raw.cast_to_varchar2('323031323031') value from dual
201201
来源：http://www.jydba.net/oracle-dump%E5%87%BD%E6%95%B0%E7%9A%84%E4%B8%8Eutl_raw/注：另外还可以使用dbms_stats.convert_raw_value函数转换。
三、自定义函数实现存储编码与日期等的转换（一）
本来打算研究DML操作中数据块、日志块和UNDO块之间的关系，但发现DUMP出来的数据都是16进制的，不易读懂。于是搜索了一些关于内部存储算法的文章，写了2个函数：bdump()和get_inner()。不尽完善，但足够读懂DUMP文件中的数据了。bdump的意思是before dump，就是返回dump之前的数据原型。get_inner的意思是获取内部数据的直观显示。字段类型需要作为入参手工输入，目前实现了以下3种类型： -- 's'表示按照字符类型进行转换 -- 'n'表示按照数值类型进行转换 -- 'd'表示按照日期类型进行转换使用举例： SQL> select my_tool.BDUMP(dump(2390.293)) from dual; MY_TOOL.BDUMP(DUMP(2390.293))
-----------------------------
2390.293
SQL> select my_tool.BDUMP(dump(';akdsfjowier2938')) from dual;
MY_TOOL.BDUMP(DUMP(';AKDSFJOWI
------------------------------
;akdsfjowier2938
SQL> select my_tool.BDUMP(dump(sysdate)) from dual;
MY_TOOL.BDUMP(DUMP(SYSDATE))
-------------------------------
2007-09-22 23:51:04 SQL> select my_tool.GET_INNER('[18] 59 55 45 43 48 41 4f 54 49 41 4e 5c 74 69 61 6e 79 63', 's') from dual; MY_TOOL.GET_INNER('[18]5955454
-------------------------------
YUECHAOTIAN\tianyc
SQL> select my_tool.GET_INNER('[ 6] c5 0c 5a 1a 5d 2f', 'n') from dual; MY_TOOL.GET_INNER('[6]C50C5A1A
-------------------------------
1189259246
SQL> select my_tool.GET_INNER('[ 7] 78 6b 09 08 16 30 1c', 'd') from dual; MY_TOOL.GET_INNER('[7]786B0908
-------------------------------
2007-09-08 21:47:27
SQL>相关代码如下。my_tool包中增加2个函数， /*------------------------------------------------------------
名称：BDUMP
目的：将DUMP出来的10进制数据返回数据原值
可以处理的类型：数值类型、字符类型、日期类型、时间戳类型
日期：2007-09-21
作者：yuechaotian
入参：为DUMP(<...>, 10)结果格式，。例如：Typ=96 Len=2: 97,100
------------------------------------------------------------*/
FUNCTION BDUMP(V_DUMP_IN VARCHAR2) RETURN VARCHAR2;/*------------------------------------------------------------
名称：GET_INNER
目的：将DUMP出来的文件中16进制数值返回10进制数据
日期：2007-09-22
作者：yuechaotian
参数v_dump_in：带长度的16进制数据（例如[ 3] c2 08 2c）
参数v_type_in：数据类型
N -- NUMBER
S -- char, varchar, varchar2
D -- DATE
------------------------------------------------------------*/
FUNCTION GET_INNER(V_DUMP_IN VARCHAR2, V_TYPE_IN VARCHAR2) RETURN VARCHAR2;my_tool包体中增加7个函数，其中 inner_to_<xx> 函数被 bdump和get_inner调用/*--------------------------INNER_TO_NUMBER------------------------
目的：将内部数值转换成可读的10进制数值
例如，输入=>193,2 输出=>1
输入=>62,100,102 输出=>-1
存在问题：如果负数总长度小于21个字节，最后加一个102。
代码中直接将102改为101进行计算。
若大于等于21字节时计算出错
-------------------------------------------------------------------*/
FUNCTION INNER_TO_NUMBER(P_DATA_MID_IN VARCHAR2, P_LENGTH_IN NUMBER)
RETURN VARCHAR2 IS
N_POWER NUMBER(38); --指数
V_DATA_MID VARCHAR2(1000); --中间数据
V_DATA_FIRST VARCHAR2(1000); --符号/指数位
N_DATA_1 NUMBER(38, 10); --1位数据
N_DATA NUMBER(38, 10) := 0; --数据
V_FLAG_NEG BOOLEAN := FALSE; --负数标志
BEGIN
V_DATA_MID := P_DATA_MID_IN || ',';
--1.获取符号/指数位
V_DATA_FIRST := TRIM(SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1));
--2获取指数
--2.1正数
IF LENGTH(V_DATA_FIRST) = 3 THEN
N_POWER := TO_NUMBER(V_DATA_FIRST) - 193;
--2.2负数
ELSE
N_POWER := 62 - TO_NUMBER(V_DATA_FIRST);
V_FLAG_NEG := TRUE;
END IF;
--3.截取中间数据（不包括指数）
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
--4.获取数据
FOR I IN 1 .. P_LENGTH_IN - 1 LOOP
--4.1截取1位数据
N_DATA_1 := TO_NUMBER(SUBSTR(V_DATA_MID,
1,
INSTR(V_DATA_MID, ',') - 1));
--4.2计算总数据
--4.2.1负数
IF V_FLAG_NEG THEN
--4.2.1.1去掉最后一位102
IF I = P_LENGTH_IN - 1 THEN
N_DATA_1 := N_DATA_1 - 1;
END IF;
--4.2.1.2计算
N_DATA := N_DATA - (101 - N_DATA_1) * POWER(100, N_POWER - (I - 1));
--4.2.2正数
ELSE
N_DATA := N_DATA + (N_DATA_1 - 1) * POWER(100, N_POWER - (I - 1));
END IF;
--4.3剔除已处理的数据
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
END LOOP;
--5.返回结果
RETURN TO_CHAR(N_DATA);
EXCEPTION
WHEN OTHERS THEN
RETURN '调用函数：INNER_TO_NUMBER 失败！错误原因：' || SQLERRM;
END INNER_TO_NUMBER;
/*------------------------INNER_TO_STRING------------------------------
目的：将内部ASCII码显示的字符以字符形式返回
例如，输入=>97,97 输出=>'aa'
-----------------------------------------------------------------------*/
FUNCTION INNER_TO_STRING(P_DATA_MID_IN VARCHAR2, P_LENGTH_IN NUMBER)
RETURN VARCHAR2 IS
V_RESULT VARCHAR2(32767); --中间字符串
V_DATA_MID VARCHAR2(1000); --中间数据
V_DATA_1 VARCHAR2(32767); --1位数据
BEGIN
V_DATA_MID := P_DATA_MID_IN || ',';
--1.根据ASCII码，计算对应的字符
FOR I IN 1 .. P_LENGTH_IN LOOP
V_DATA_1 := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
V_RESULT := V_RESULT || CHR(V_DATA_1);
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
END LOOP;
--2.返回结果
RETURN V_RESULT;
EXCEPTION
WHEN OTHERS THEN
RETURN '调用函数：INNER_TO_STRING 失败！错误原因：' || SQLERRM;
END INNER_TO_STRING;
/*------------------------INNER_TO_DATE_1------------------------------
目的：将磁盘内部显示的日期以字符形式返回
例如，输入=>'120,107,9,22,13,32,4' 输出=>'2007-09-22 12:31:03'
-----------------------------------------------------------------------*/
FUNCTION INNER_TO_DATE_1(P_DATA_MID_IN VARCHAR2, P_LENGTH_IN NUMBER)
RETURN VARCHAR2 IS
V_RESULT VARCHAR2(32767); --中间字符串
V_DATA_MID VARCHAR2(1000); --中间数据
V_DATA_1 VARCHAR2(32767); --1位数据
BEGIN
V_DATA_MID := P_DATA_MID_IN || ',';
--1.计算日期类型
FOR I IN 1 .. P_LENGTH_IN LOOP
V_DATA_1 := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
-- 1.1世纪、年度
IF I < 3 THEN
V_DATA_1 := TO_CHAR(TO_NUMBER(V_DATA_1 - 100));
-- 1.2时 分 秒
ELSIF I > 4 THEN
V_DATA_1 := TO_CHAR(TO_NUMBER(V_DATA_1 - 1));
END IF;
V_RESULT := V_RESULT || LPAD(V_DATA_1, 2, '0');
-- 1.3剔除已处理的数据
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
END LOOP;
--2.返回指定格式的日期类型
RETURN TO_CHAR(TO_DATE(V_RESULT, 'yyyymmddhh24miss'),
'yyyy-mm-dd hh24:mi:ss');
EXCEPTION
WHEN OTHERS THEN
RETURN '调用函数：INNER_TO_DATE_1 失败！错误原因：' || SQLERRM;
END INNER_TO_DATE_1;
/*------------------------INNER_TO_DATE_2------------------------------
目的：将内存内部显示的日期以字符形式返回
例如，输入=>'215,7,9,22,12,31,3,0' 输出=>'2007-09-22 12:31:03'
-----------------------------------------------------------------------*/
FUNCTION INNER_TO_DATE_2(P_DATA_MID_IN VARCHAR2, P_LENGTH_IN NUMBER)
RETURN VARCHAR2 IS
V_RESULT VARCHAR2(32767); --中间字符串
V_DATA_MID VARCHAR2(1000); --中间数据
V_DATA_1 VARCHAR2(32767); --1位数据
BEGIN
V_DATA_MID := P_DATA_MID_IN || ',';
-- 1.处理世纪、年度
--1.1获取第1位的16进制数据
V_DATA_1 := TRIM(TO_CHAR(TO_NUMBER(SUBSTR(V_DATA_MID,
1,
INSTR(V_DATA_MID, ',') - 1)),
'xxx'));
--1.2截取后7位
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
--1.3将前两位反拼
V_DATA_1 := TRIM(SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1)) ||
V_DATA_1;
--1.4转换成10进制的世纪、年度
V_DATA_1 := TO_CHAR(TO_NUMBER(V_DATA_1, 'xxx'));
V_RESULT := V_RESULT || V_DATA_1;
/*处理世纪、年度的另一种方法
-- 1.1 获取第1位
V_DATA_1 := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
--1.2截取后7位
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
--1.3计算世纪、年度
V_DATA_1 := V_DATA_1 + TRIM(SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1)) * 256;
V_RESULT := V_RESULT || V_DATA_1;
*/
--2截取后6位，用以计算 月 日 时 分 秒
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
--3.计算 月 日 时 分 秒
FOR I IN 1 .. P_LENGTH_IN - 3 LOOP
V_DATA_1 := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
V_RESULT := V_RESULT || LPAD(V_DATA_1, 2, '0');
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
END LOOP;
--4.返回指定格式的日期类型
RETURN TO_CHAR(TO_DATE(V_RESULT, 'yyyymmddhh24miss'),
'yyyy-mm-dd hh24:mi:ss');
EXCEPTION
WHEN OTHERS THEN
RETURN '调用函数：INNER_TO_DATE_2 失败！错误原因：' || SQLERRM;
END INNER_TO_DATE_2; /*------------------------INNER_TO_STAMP_1------------------------------
目的：将磁盘内部显示的时间戳以字符形式返回
例如，输入=>'120,107,9,22,14,41,5,14,230,178,128'
输出=>'2007-09-22 13:40:04.250000000'
-----------------------------------------------------------------------*/
FUNCTION INNER_TO_STAMP_1(P_DATA_MID_IN VARCHAR2, P_LENGTH_IN NUMBER)
RETURN VARCHAR2 IS
V_RESULT VARCHAR2(32767); --中间字符串
V_DATA_MID VARCHAR2(1000); --中间数据
V_DATA_1 VARCHAR2(32767); --1位数据
V_FF VARCHAR2(32767); --微秒
BEGIN
V_DATA_MID := P_DATA_MID_IN || ',';
--1.计算日期类型
FOR I IN 1 .. P_LENGTH_IN - 4 LOOP
V_DATA_1 := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
-- 1.1世纪、年度
IF I < 3 THEN
V_DATA_1 := TO_CHAR(TO_NUMBER(V_DATA_1 - 100));
-- 1.2时 分 秒
ELSIF I > 4 THEN
V_DATA_1 := TO_CHAR(TO_NUMBER(V_DATA_1 - 1));
END IF;
V_RESULT := V_RESULT || LPAD(V_DATA_1, 2, '0');
-- 1.3剔除已处理的数据
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
END LOOP;
--2.计算微秒
--2.1 将10进制微秒拼成16进制串
FOR I IN 1 .. 4 LOOP
V_DATA_1 := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
V_FF := V_FF || TRIM(TO_CHAR(TO_NUMBER(V_DATA_1), 'xxx'));
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
END LOOP;
--2.2 将16进制微秒转换成10进制微秒
V_FF := TRIM(TO_CHAR(TO_NUMBER(V_FF, 'xxxxxxxxxxxxx')));
--3.返回指定格式的时间戳类型
V_RESULT := V_RESULT || '.' || V_FF;
RETURN TO_CHAR(TO_TIMESTAMP(V_RESULT, 'yyyymmddhh24miss.ff'),
'yyyy-mm-dd hh24:mi:ss.ff');
EXCEPTION
WHEN OTHERS THEN
RETURN '调用函数：INNER_TO_DATE_1 失败！错误原因：' || SQLERRM;
END INNER_TO_STAMP_1; /*----------------------------------
目的：将DUMP出来的数值类型的数据返回数据原值
日期：2007-09-22
----------------------------------*/
FUNCTION BDUMP(V_DUMP_IN VARCHAR2) RETURN VARCHAR2 IS
V_TYPE VARCHAR2(20); --类型
N_LENGTH NUMBER(38); --长度
V_DATA VARCHAR2(1000); --数据
V_DATA_MID VARCHAR2(1000); --中间数据
BEGIN
--1.获取类型
V_TYPE := SUBSTR(V_DUMP_IN,
INSTR(V_DUMP_IN, '=') + 1,
INSTR(V_DUMP_IN, ' ') - INSTR(V_DUMP_IN, '=') - 1);
--2.获取总长度
N_LENGTH := TO_NUMBER(SUBSTR(V_DUMP_IN,
INSTR(V_DUMP_IN, '=', 5) + 1,
INSTR(V_DUMP_IN, ':') -
INSTR(V_DUMP_IN, '=', 5) - 1));
--3.截取中间数据（去掉头信息）
V_DATA_MID := SUBSTR(V_DUMP_IN, INSTR(V_DUMP_IN, ':') + 1);
--4.获取数据
CASE V_TYPE
-- 4.1 number类型
WHEN '2' THEN
V_DATA := INNER_TO_NUMBER(V_DATA_MID, N_LENGTH);
-- 4.2 char类型
WHEN '96' THEN
V_DATA := INNER_TO_STRING(V_DATA_MID, N_LENGTH);
-- 4.2 varchar2和varchar类型
WHEN '1' THEN
V_DATA := INNER_TO_STRING(V_DATA_MID, N_LENGTH);
-- 4.3 date类型（磁盘存储格式）
WHEN '12' THEN
V_DATA := INNER_TO_DATE_1(V_DATA_MID, N_LENGTH);
-- 4.4 date类型（内存存储格式）
WHEN '13' THEN
V_DATA := INNER_TO_DATE_2(V_DATA_MID, N_LENGTH);
-- 4.5 timestamp类型（磁盘存储格式）
WHEN '180' THEN
V_DATA := INNER_TO_STAMP_1(V_DATA_MID, N_LENGTH);
END CASE;
RETURN V_DATA;
EXCEPTION
WHEN OTHERS THEN
RETURN('调用函数BDUMP失败！错误原因:' || SQLERRM);
END BDUMP;/*------------------------------------------------------------
目的：将DUMP出来的16进制数值返回10进制数据
日期：2007-09-22
作者：yuechaotian
输入=>[ 2] c1 02，输出=>
------------------------------------------------------------*/
FUNCTION GET_INNER(V_DUMP_IN VARCHAR2, V_TYPE_IN VARCHAR2) RETURN VARCHAR2 IS
V_RESULT VARCHAR2(32767); --返回结果
V_DATA_MID VARCHAR2(32767); --中间数据
V_TEN VARCHAR2(32767); --中间数据（10进制）
V_DATA_1 VARCHAR2(32767); --1位数据
N_LENGTH NUMBER(10); --长度
BEGIN
V_DATA_MID := V_DUMP_IN;
--获取长度
N_LENGTH := TO_NUMBER(TRIM(SUBSTR(V_DATA_MID, 2, 2)));
--截取DUMP数据（去掉头信息），并替换空格为逗号
V_DATA_MID := REPLACE(SUBSTR(V_DATA_MID, 7), CHR(32), ',') || ',';
--替换16进制数据为10进制数据
FOR I IN 1 .. N_LENGTH LOOP
V_DATA_1 := SUBSTR(V_DATA_MID, 1, INSTR(V_DATA_MID, ',') - 1);
V_TEN := V_TEN || TO_NUMBER(V_DATA_1, 'xx') || ',';
V_DATA_MID := SUBSTR(V_DATA_MID, INSTR(V_DATA_MID, ',') + 1);
END LOOP;
V_TEN := SUBSTR(V_TEN, 1, LENGTH(V_TEN) - 1);
CASE UPPER(V_TYPE_IN)
WHEN 'N' THEN
V_RESULT := INNER_TO_NUMBER(V_TEN, N_LENGTH);
WHEN 'S' THEN
V_RESULT := INNER_TO_STRING(V_TEN, N_LENGTH);
WHEN 'D' THEN
V_RESULT := INNER_TO_DATE_1(V_TEN, N_LENGTH);
END CASE;
RETURN V_RESULT;
EXCEPTION
WHEN OTHERS THEN
RETURN('调用函数GET_INNER失败！错误原因:' || SQLERRM);
END GET_INNER;
来源：http://biancheng.dnbcw.info/oracle/300289.html
三、自定义函数实现存储编码与日期等的转换（二）
前一阵写了Oracle基本数据类型存储格式浅析，对各种数量类型的存储进行了简单的描述，而后又写了一篇repare包修复坏块，其中自己写了一个程序包来恢复DUMP后的数据。但是那个程序包主要是针对repare包生成的结果的，因此通用性不好。
这篇文章将那个程序包修改并简化，变为一个函数。下面给出这个函数的实现和使用例子：
SQL> CREATE OR REPLACE FUNCTION F_GET_FROM_DUMP
2 (
3 P_DUMP IN VARCHAR2,
4 P_TYPE IN VARCHAR2
5 )
6 RETURN VARCHAR2 AS
7 V_LENGTH_STR VARCHAR2(10);
8 V_LENGTH NUMBER DEFAULT 7;
9 V_DUMP_ROWID VARCHAR2(30000);
10
11 V_DATE_STR VARCHAR2(100);
12 TYPE T_DATE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
13 V_DATE T_DATE;
14
15 FUNCTION F_ADD_PREFIX_ZERO (P_STR IN VARCHAR2, P_POSITION IN NUMBER) RETURN VARCHAR2
16 AS
17 V_STR VARCHAR2(30000) := P_STR;
18 V_POSITION NUMBER := P_POSITION;
19 V_STR_PART VARCHAR2(2);
20 V_RETURN VARCHAR2(30000);
21 BEGIN
22 WHILE (V_POSITION != 0) LOOP
23 V_STR_PART := SUBSTR(V_STR, 1, V_POSITION - 1);
24 V_STR := SUBSTR(V_STR, V_POSITION + 1);
25
26 IF V_POSITION = 2 THEN
27 V_RETURN := V_RETURN || '0' || V_STR_PART;
28 ELSIF V_POSITION = 3 THEN
29 V_RETURN := V_RETURN || V_STR_PART;
30 ELSE
31 RAISE_APPLICATION_ERROR(-20002, 'DUMP ERROR CHECK THE INPUT ROWID');
32 END IF;
33
34 V_POSITION := INSTR(V_STR, ',');
35 END LOOP;
36 RETURN REPLACE(V_RETURN , ',');
37 END F_ADD_PREFIX_ZERO;
38
39 BEGIN
40 IF SUBSTR(P_DUMP, 1, 3) = 'Typ' THEN
41 V_DUMP_ROWID := SUBSTR(P_DUMP, INSTR(P_DUMP, ':') + 2);
42 ELSE
43 V_DUMP_ROWID := P_DUMP;
44 END IF;
45
46 IF P_TYPE = 'VARCHAR2' OR P_TYPE = 'CHAR' THEN
47
48 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));
49
50 RETURN(UTL_RAW.CAST_TO_VARCHAR2(V_DUMP_ROWID));
51
52 ELSIF P_TYPE = 'NUMBER' THEN
53
54 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));
55
56 RETURN(TO_CHAR(UTL_RAW.CAST_TO_NUMBER(V_DUMP_ROWID)));
57
58 ELSIF P_TYPE = 'DATE' THEN
59
60 V_DUMP_ROWID := ',' || V_DUMP_ROWID || ',';
61
62 FOR I IN 1..7 LOOP
63 V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, ',', 1, I) + 1,
64 INSTR(V_DUMP_ROWID, ',', 1, I + 1) - INSTR(V_DUMP_ROWID, ',', 1, I) - 1), 'XXX');
65 END LOOP;
66
67 V_DATE(1) := V_DATE(1) - 100;
68 V_DATE(2) := V_DATE(2) - 100;
69
70 IF ((V_DATE(1) < 0) OR (V_DATE(2) < 0)) THEN
71 V_DATE_STR := '-' || LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)), '
00'));
72 ELSE
73 V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)),'00'));
74 END IF;
75
76 V_DATE_STR := V_DATE_STR || '-' || TO_CHAR(V_DATE(3)) || '-' || TO_CHAR(V_DATE(4)) || ' ' ||
77 TO_CHAR(V_DATE(5) - 1) || ':' || TO_CHAR(V_DATE(6) - 1) || ':' || TO_CHAR(V_DATE(7) - 1);
78 RETURN (V_DATE_STR);
79
80 ELSIF ((P_TYPE LIKE 'TIMESTAMP(_)') OR (P_TYPE = 'TIMESTAMP')) THEN
81
82 V_DUMP_ROWID := ',' || V_DUMP_ROWID || ',';
83
84 FOR I IN 1..11 LOOP
85 V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, ',', 1, I) + 1,
86 INSTR(V_DUMP_ROWID, ',', 1, I + 1) - INSTR(V_DUMP_ROWID, ',', 1, I) - 1), 'XXX');
87 END LOOP;
88
89 V_DATE(1) := V_DATE(1) - 100;
90 V_DATE(2) := V_DATE(2) - 100;
91
92 IF ((V_DATE(1) < 0) OR (V_DATE(2) < 0)) THEN
93 V_DATE_STR := '-' || LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)), '
00'));
94 ELSE
95 V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)),'00'));
96 END IF;
97
98 V_DATE_STR := V_DATE_STR || '-' || TO_CHAR(V_DATE(3)) || '-' || TO_CHAR(V_DATE(4)) || ' ' ||
99 TO_CHAR(V_DATE(5) - 1) || ':' || TO_CHAR(V_DATE(6) - 1) || ':' || TO_CHAR(V_DATE(7) - 1) ||
'.' ||
100 SUBSTR(TO_CHAR(V_DATE(8) * POWER(256, 3) + V_DATE(9) * POWER(256, 2) + V_DATE(10) * 256 + V_
DATE(11)),
101 1, NVL(TO_NUMBER(SUBSTR(P_TYPE, 11, 1)), 6));
102 RETURN (V_DATE_STR);
103
104 ELSIF P_TYPE = 'RAW' THEN
105
106 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));
107
108 RETURN(V_DUMP_ROWID);
109
110 ELSIF P_TYPE = 'ROWID' THEN
111
112 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));
113 RETURN (DBMS_ROWID.ROWID_CREATE(
114 1,
115 TO_NUMBER(SUBSTR(V_DUMP_ROWID, 1, 8), 'XXXXXXXXXXX'),
116 TRUNC(TO_NUMBER(SUBSTR(V_DUMP_ROWID, 9, 4), 'XXXXXX')/64),
117 TO_NUMBER(MOD(TO_NUMBER(SUBSTR(V_DUMP_ROWID, 9, 4), 'XXXXXX'), 64) ||
118 TO_NUMBER(SUBSTR(V_DUMP_ROWID, 13, 4), 'XXXXXXXXXXX')),
119 TO_NUMBER(SUBSTR(V_DUMP_ROWID, 17, 4), 'XXXXXX')));
120
121 ELSE
122 RAISE_APPLICATION_ERROR(-20001, 'TYPE NOT VALID OR CAN''T TRANSALTE ' || P_TYPE || ' TYPE');
123 END IF;
124
125 END;
126 /
函数已创建。
SQL> SELECT F_GET_FROM_DUMP(DUMP(2342.231, 16), 'NUMBER') FROM DUAL;
F_GET_FROM_DUMP(DUMP(2342.231,16),'NUMBER')
--------------------------------------------
2342.231
SQL> SELECT F_GET_FROM_DUMP(DUMP(-0.00234, 16), 'NUMBER') FROM DUAL;
F_GET_FROM_DUMP(DUMP(-0.00234,16),'NUMBER')
---------------------------------------------
-.00234
SQL> SELECT F_GET_FROM_DUMP(DUMP('23EJF.M>', 16), 'VARCHAR2') FROM DUAL;
F_GET_FROM_DUMP(DUMP('23EJF.M>',16),'VARCHAR2')
------------------------------------------------
23EJF.M>
SQL> SELECT F_GET_FROM_DUMP(DUMP('测试', 16), 'VARCHAR2') FROM DUAL;
F_GET_FROM_DUMP(DUMP('测试',16),'VARCHAR2')
------------------------------------------------
测试
由于在SQL中直接使用DATE类型和Oracle存储的不一致，因此解析DATE和TIMESTAMP类型需要通过表中存储的数据，而不能通过SQL中的TO_DATE或SYSDATE。在SQL中直接使用的DATE类型的解析由于意义不大而没有给出。关于在SQL中直接使用DATE和存储在表中的DATE类型的区别，可以参考我的Oracle基本数据类型存储格式浅析中日期类型的文章，连接在文章末尾给出。
SQL> CREATE TABLE TEST_DATE (TIME1 DATE, TIME2 TIMESTAMP, TIME3 TIMESTAMP(9));
表已创建。
SQL> INSERT INTO TEST_DATE VALUES (SYSDATE,
2 TO_TIMESTAMP('2004-4-9 22:59:43.234232222', 'YYYY-MM-DD HH24:MI:SS.FF'),
3 TO_TIMESTAMP('2004-4-9 22:59:43.234232222', 'YYYY-MM-DD HH24:MI:SS.FF'));
已创建 1 行。
SQL> COL GET_DUMP FORMAT A30
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。
SQL> SELECT TIME1, F_GET_FROM_DUMP(DUMP(TIME1, 16), 'DATE') GET_DUMP FROM TEST_DATE;
TIME1 GET_DUMP
------------------- ------------------------------
2005-04-09 23:00:04 2005-4-9 23:0:4
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
会话已更改。
SQL> SELECT TIME2, F_GET_FROM_DUMP(DUMP(TIME2, 16), 'TIMESTAMP') GET_DUMP
2 FROM TEST_DATE;
TIME2 GET_DUMP
---------------------------------- ------------------------
2004-04-09 22:59:43.234232 2004-4-9 22:59:43.234232
SQL> SELECT TIME3, F_GET_FROM_DUMP(DUMP(TIME3, 16), 'TIMESTAMP(9)') GET_DUMP
2 FROM TEST_DATE;
TIME3 GET_DUMP
---------------------------------- ------------------------
2004-04-09 22:59:43.234232222 2004-4-9 22:59:43.234232222
对于SQL中直接使用的DATE类型会报错：
[PHP]
SQL> SELECT SYSDATE, F_GET_FROM_DUMP(DUMP(SYSDATE, 16), 'DATE') GET_DUMP FROM DUAL;
SYSDATE GET_DUMP
------------------- ------------------------------
2005-04-09 23:04:58 -###93-4-9 22:3:57
SQL> SELECT RAW_DATA, F_GET_FROM_DUMP(DUMP(RAW_DATA, 16), 'RAW') GET_DUMP
2 FROM TEST_RAW;
RAW_DATA GET_DUMP
-------------------- ------------------------------
F5021C f5021c
这个函数目前支持CHAR、VARCHAR2、NUMBER、DATE、TIMESTAMP和RAW类型，上面分别举了例子。
函数的第一个参数可以是DUMP函数的输出，也可以是数据库中的直接存储信息（需要用逗号分隔）。
SQL> SELECT F_GET_FROM_DUMP('Typ=96 Len=4: 74,65,73,74', 'VARCHAR2') GET_DUMP
2 FROM DUAL;
GET_DUMP
------------------------------
test
SQL> SELECT F_GET_FROM_DUMP('74,65,73,74', 'VARCHAR2') GET_DUMP
2 FROM DUAL;
GET_DUMP
------------------------------
test
编写这个函数所根据的规则来自下列文章：
Oracle基本数据类型存储格式浅析（一）——字符类型：http://blog.itpub.net/post/468/9287
Oracle基本数据类型存储格式浅析（二）——数字类型：http://blog.itpub.net/post/468/9445
Oracle基本数据类型存储格式浅析（三）——日期类型（一）：http://blog.itpub.net/post/468/10113
Oracle基本数据类型存储格式浅析（三）——日期类型（二）：http://blog.itpub.net/post/468/10293
Oracle基本数据类型存储格式浅析（三）——日期类型（三）：http://blog.itpub.net/post/468/10582
Oracle基本数据类型存储格式浅析（三）——日期类型（四）：http://blog.itpub.net/post/468/13636
Oracle基本数据类型存储格式浅析（四）——ROWID类型（一）：http://blog.itpub.net/post/468/11046
Oracle基本数据类型存储格式浅析（四）——ROWID类型（二）：http://blog.itpub.net/post/468/11363
Oracle基本数据类型存储格式浅析（五）——RAW类型：http://blog.itpub.net/post/468/11490
这个函数是由下面这个文章中的包进行修改的：
DBMS_REPAIR的使用（二）：http://blog.itpub.net/post/468/13241
来源：http://www.itpub.net/forum.php?mod=viewthread&action=printable&tid=308317