来源: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