一、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
